STEEM SQL 系列之 每个月到底能挣多少?SteemSQL Tutorial: What is Your Monthly Income on Steemit?

Thank you @arcange for creating STEEMSQL!

STEEM SQL Tutorial Series:

Have you wondered that the your earnings on STEEMIT can really make a difference? Have you thought about making a living by full time blogging on steemit? The easiest way is to look at your historic earning data monthly so you get a rough idea via the following SQL

1
2
3
4
5
6
7
8
9
10
select 
FORMAT(created, 'yyyy-MM') Month, sum(total_payout_value) Min, sum(total_payout_value) + sum(curator_payout_value) Max
from
Comments (NOLOCK)
where
author='justyy'
group by
FORMAT(created, 'yyyy-MM')
order by
FORMAT(created, 'yyyy-MM') desc

We know that 75% are author rewards, so if we group the data by months, sum the author rewards, that will be the minimal level of payout. Suppose you get all other 25% curation rewards e.g. always upvotes at 30 min, that is the maximum (in theory) you earn.

In my case, here is my earning data per calendar month, I have no idea why the Sept (this month) is zero earnings (maybe data is not synchronized into steemSQL from the blockchain yet?)

It would be great if you could share yours in the comments (the data is public anyway). In 7 days, I will give 1 SBD to whoever has the lowest and highest payout respectively (in August 2017).


Image Credit: Pixabay.com

感谢 @arcange 创造了 STEEMSQL!

STEEM SQL 系列:

使用 LINQPAD + STEEMSQL,我们可以跑一下下面的SQL,来看看我每个月在STEEMIT上的收入情况:

1
2
3
4
5
6
7
8
9
10
select 
FORMAT(created, 'yyyy-MM') 月份, sum(total_payout_value) 最小收入, sum(total_payout_value) + sum(curator_payout_value) 最大收入
from
Comments (NOLOCK)
where
author='justyy'
group by
FORMAT(created, 'yyyy-MM')
order by
FORMAT(created, 'yyyy-MM') desc

75%是作者奖励,25%是点赞奖励,所以最少拿到75%,25%理论最大值(但远到不了)。奇怪的是9月份(这个月)数据为0,也许是数据还没从区块链同步到 steemsql 上。

欢迎把你们的收入在评论中晒一晒,反正这些数据都是公开的。7天后,我会给评论中2017年8月份最低和最高收入的各送1 SBD。

// Later, it may be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客

Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.

原文首发于 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: STEEM SQL 系列之 每个月到底能挣多少?SteemSQL Tutorial: What is Your Monthly Income on Steemit?

Daily #CN Updates - CN社区每日榜单【优质内容机器人点赞记录】【过去7天潜在收益排行榜】【那些优秀被错过的文章】(2017-09-25)

数据来源: steemsql.com
生成时间: 2017-09-25 11:11:03 (UTC)
报告时间: 2017-09-21 11:11:03 (UTC) to 2017-09-22 11:11:03 (UTC)

2017-09-25 那些优秀可能被错过的文章


Image Credit: pixabay.com
| | 作者 Author| 文章 Post|
|———-|:————-:|:——|
| 1 | @mrpointp | There is always someone keeping working out (part 33)(Original) /总有一个比你忙,比你穷,比你老的人在健身(三十三) |
| 2 | @fundurian | 想搬砖的故事moving brick |
| 3 | @aafeng | 国外经典图书推荐之:查理和巧克力工厂 |
| 4 | @drunkevil | steemit文章排版中标题距离下方内容太近的两种解决方案 |
| 5 | @icedream | 八朝古都,河南开封,天波杨府 |
| 6 | @nationalpark | Saint Kitts and Nevis 圣基茨和尼维斯 ?? |
| 7 | @liangfengyouren | 两个多月Steemit写作总结,赚了676.46美元 |
| 8 | @icedream | 那年中秋,思乡浓浓——the Mid-autumn Festival |
| 9 | @shenchensucc | Hero coin ICO and free Hero coin claim 免费领取Hero币和Hero币ICO |
| 10 | @zsilence | Steemit 开发新进展 |
符合条件的文章总数: 25

2017-09-25 Daily Top Authors in [CN] (Last 7 Days) 每日cn社区之 过去7天潜在收益排行榜

The following is the list of top 30 daily authors in [CN] in the last 7 days sorted by potential payout. See the SQL for more details.
这是根据这篇的SQL - 经少量修改和调整统计出过去7天作者的潜在收益排行。
| 排名 Rank| 作者 Author| 发贴数 Posts| 点赞数 Votes| 潜在收益 Pending Payout| 平均每贴收益 Average Pending Payout Per Post|
|–:|–:|–:|–:|—:|—:|
| 1| @oflyhigh | 6 | 2365 | 1292.69 |215.45 |
| 2| @deanliu | 6 | 2056 | 1042.32 |173.72 |
| 3| @linuslee0216 | 8 | 1828 | 1034.20 |129.28 |
| 4| @htliao | 7 | 1692 | 905.85 |129.41 |
| 5| @rivalhw | 6 | 1454 | 880.35 |146.72 |
| 6| @jubi | 11 | 1672 | 676.97 |61.54 |
| 7| @coldhair | 12 | 1652 | 657.69 |54.81 |
| 8| @justyy | 17 | 2330 | 656.11 |38.59 |
| 9| @tumutanzi | 5 | 1203 | 651.57 |130.31 |
| 10| @guyverckw | 9 | 1120 | 621.64 |69.07 |
| 11| @blackbunny | 6 | 1121 | 596.52 |99.42 |
| 12| @twinkledrop | 5 | 656 | 573.31 |114.66 |
| 13| @hannahwu | 8 | 914 | 548.41 |68.55 |
| 14| @ace108 | 29 | 3097 | 477.69 |16.47 |
| 15| @wilkinshui | 6 | 1129 | 436.02 |72.67 |
| 16| @sweetsssj | 1 | 1659 | 422.10 |422.10 |
| 17| @rea | 2 | 960 | 420.24 |210.12 |
| 18| @someone | 3 | 819 | 417.30 |139.10 |
| 19| @joythewanderer | 5 | 1084 | 404.52 |80.90 |
| 20| @krischy | 3 | 823 | 396.29 |132.10 |
| 21| @kitcat | 5 | 967 | 385.14 |77.03 |
| 22| @jessicameng | 7 | 453 | 366.80 |52.40 |
| 23| @aaronli | 4 | 583 | 361.25 |90.31 |
| 24| @dapeng | 7 | 977 | 360.08 |51.44 |
| 25| @kenchung | 4 | 760 | 316.58 |79.15 |
| 26| @btsabc | 5 | 662 | 308.14 |61.63 |
| 27| @victorier | 5 | 677 | 294.36 |58.87 |
| 28| @goodboyphilip | 8 | 291 | 285.27 |35.66 |
| 29| @tvb | 5 | 605 | 274.52 |54.90 |
| 30| @sunnyjolly | 7 | 500 | 267.52 |38.22 |

以上收益包括75%的作者收益(Author Rewards)和25%的点赞收益(Curation Rewards)。
@dailystats 提供过去7天全网潜在收益前30名的排名
check @dailystats for top 30 daily authors in the last 7 days sorted by potential payout

CN 区优质内容点赞机器人每天会为这30名作者点赞!
CN Good-Content-Body will upvote these authors every day!

近期机器人点赞记录 Recent Quality-Content-Upvote-Bot Upvoting History

这个机器人很善良,只想激励CN社区产生更多的优质内容。当然别忘记了,我本人人工也是会对未上榜的优质作者进行点赞的哟~
The quality-content-upvoting-bot motivates for more good contents in [CN], however, I will also upvote other authors if good content are produced.

时间 Time 作者 Author 文章 Post 权重 Weighting % VP %
2017-09-24 00:00:42 @travelgirl @travelgirl 14.54 66.09
2017-09-24 00:18:17 @rea @rea 14.55 66.12
2017-09-24 02:00:41 @nicolemoker @nicolemoker 12.68 63.42
2017-09-24 03:21:32 @ace108 @ace108 14.16 64.36
2017-09-24 03:38:26 @rivalhw @rivalhw 18.03 64.40
2017-09-24 03:38:22 @rivalhw @rivalhw 18.03 64.40
2017-09-24 04:55:39 @deanliu @deanliu 19.46 64.86
2017-09-24 05:16:19 @linuslee0216 @linuslee0216 18.17 64.89
2017-09-24 05:33:24 @mrspointm @mrspointm 14.27 64.88
2017-09-24 09:13:34 @jubi @jubi 18.47 65.97
2017-09-24 10:08:12 @htliao @htliao 17.11 61.11
2017-09-24 12:47:00 @coldhair @coldhair 15.89 61.13
2017-09-24 12:50:37 @oflyhigh @oflyhigh 18.29 60.98
2017-09-24 13:28:21 @aaronli @aaronli 13.48 61.27
2017-09-24 13:45:33 @someone @someone 13.49 61.34
2017-09-24 13:49:15 @wilkinshui @wilkinshui 14.69 61.21
2017-09-24 13:54:20 @kitcat @kitcat 13.44 61.10
2017-09-24 14:53:58 @guyverckw @guyverckw 17.33 61.91
2017-09-24 14:52:08 @guyverckw @guyverckw 17.33 61.88
2017-09-24 16:17:14 @blackbunny @blackbunny 16.26 62.56
2017-09-24 18:00:26 @btsabc @btsabc 12.75 63.77

每日’那些优秀被错过的文章’ 排行榜几点说明:

  • 取的是发贴在3 到4天前的 10个较少收益(较少被关注,但同时质量又较不错)的帖子。
  • 生成的结果去掉了 我自己的帖子 @justyy
  • 有可能会微调参数让结果更合理。
  • 查询SQL语句和参数暂时不公开,因为这样会更公平一些。

这个报告的年龄为: 31天。 i.e. 发布的第一天, 发布的第二天
为什么要有这个报告?
欢迎 @justyy 如果您有好的建议或者想看哪些榜单却找不到。
Tags: #cn

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: Daily #CN Updates - CN社区每日榜单【优质内容机器人点赞记录】【过去7天潜在收益排行榜】【那些优秀被错过的文章】(2017-09-25)

看看谁是你把Steem Power能量代理给你了? Online Tool to Check Who Delegate SP to Your STEEMIT Account


Image Credit: Pixabay.com

My Previous Steemit Tools:

Today, I have added another tool to check who delegates the SP to you. For example, the @justyy upvoting bot for CN community has received many SP delgations, thanks for support!

A free-to-use-and-very-powerful-and-convenient API is also provided but use it at your own risk!

Oh, almost forgot, the Steemit Delegator Checker is available at:

https://helloacm.com/tools/steemit/delegators/

我之前弄的 STEEMIT 工具

今天 我又撸了一工具:用来查看谁把SP代理给你了。万一哪天 ned 临幸了呢?梦想还是要有的。

感谢 所有代理 SP给 @justyy CN区点赞机器人!你们是最棒的!

当然,和以往一样, API照样免费使用。你可以尽情的整合到你的工具中去,避免重复造轮子。

差点忘记了,这是STEEMIT 代理查询工具地址

https://helloacm.com/tools/steemit/list-of-delegators/

CN 每日排行榜
Daily Top 30 Authors Pending Payout in the Last 7 days

// Later, it may be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客

Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.

原文首发于 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: 看看谁是你把Steem Power能量代理给你了? Online Tool to Check Who Delegate SP to Your STEEMIT Account

看看到底谁踩(downvote)了你的帖子? Online Tool to Check Who Downvote Your Posts/Comments


Image Credit: Pixabay.com

I have written an online tool to check who downvoted you in the last 365 days.

https://helloacm.com/tools/steemit/who-downvote-you/

Enter your STEEM ID, and press Enter you should get a list of downvoters very quickly.

The API is also made public free to use so that you can integrate in your tools/applications.

被人踩是不是不太爽?最不爽的是不是别人踩了你,你却不知道?这下好了,花了一小时,做了以下在线STEEMIT 工具,很方便,谁踩了你,一清二楚。

https://helloacm.com/tools/steemit/who-downvote-you-steemit/

输入ID,按回车即可查询:

同时,该功能的API 已经提供,返回JSON数据, 比如 可以很方便的集成到 @oflyhigh O 哥的公众号 steemit 上。

CN 每日排行榜

// Later, it may be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客

Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.

原文首发于 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: 看看到底谁踩(downvote)了你的帖子? Online Tool to Check Who Downvote Your Posts/Comments

Daily #CN Updates - CN社区每日榜单【优质内容机器人点赞记录】【过去7天潜在收益排行榜】【那些优秀被错过的文章】(2017-09-24)

数据来源: steemsql.com
生成时间: 2017-09-24 11:11:33 (UTC)
报告时间: 2017-09-20 11:11:33 (UTC) to 2017-09-21 11:11:33 (UTC)

2017-09-24 那些优秀可能被错过的文章


Image Credit: pixabay.com
| | 作者 Author| 文章 Post|
|———-|:————-:|:——|
| 1 | @incrediblesnow | 公开小猎豹赞助SP详情 |
| 2 | @mrspointm | A Little Ant is Storing Food for the Coming Winter /小蚂蚁之秋存冬粮 (Original) 谷歌点名 #8 |
| 3 | @fundurian | 谷哥点名#8 春秋战国 |
| 4 | @mxg | 大湖仙山游记 |
| 5 | @nationalpark | Barbados 巴巴多斯岛 ?? |
| 6 | @incrediblesnow | 20/9/2017 Cn-abuse 的报告 ( Report for Cn-abuse on 20/9/2017) |
| 7 | @liflorence | Li’s Voyage# travel with me to London, UK ???? 我的旅行记之跟我一起去英国伦敦 |
| 8 | @icedream | 工作不开心,何解? |
| 9 | @shenchensucc | Hero coin ICO and free Hero coin claim 免费领取Hero币和Hero币ICO |
| 10 | @shieha | ?? Four Four South Village Monochromes 四四南村-眷村黑白攝影集 |
符合条件的文章总数: 25

2017-09-24 Daily Top Authors in [CN] (Last 7 Days) 每日cn社区之 过去7天潜在收益排行榜

The following is the list of top 30 daily authors in [CN] in the last 7 days sorted by potential payout. See the SQL for more details.
这是根据这篇的SQL - 经少量修改和调整统计出过去7天作者的潜在收益排行。
| 排名 Rank| 作者 Author| 发贴数 Posts| 点赞数 Votes| 潜在收益 Pending Payout| 平均每贴收益 Average Pending Payout Per Post|
|–:|–:|–:|–:|—:|—:|
| 1| @oflyhigh | 7 | 2788 | 1520.79 |217.26 |
| 2| @deanliu | 6 | 2039 | 1037.04 |172.84 |
| 3| @rivalhw | 7 | 1571 | 1021.05 |145.86 |
| 4| @tumutanzi | 7 | 1458 | 931.21 |133.03 |
| 5| @htliao | 7 | 1633 | 928.79 |132.68 |
| 6| @linuslee0216 | 7 | 1650 | 898.27 |128.32 |
| 7| @jubi | 13 | 1766 | 760.17 |58.47 |
| 8| @guyverckw | 10 | 1261 | 676.55 |67.65 |
| 9| @justyy | 18 | 2419 | 645.70 |35.87 |
| 10| @blackbunny | 5 | 979 | 547.82 |109.56 |
| 11| @coldhair | 10 | 1395 | 537.53 |53.75 |
| 12| @hannahwu | 7 | 884 | 507.87 |72.55 |
| 13| @joythewanderer | 6 | 1196 | 464.89 |77.48 |
| 14| @dapeng | 8 | 1109 | 459.68 |57.46 |
| 15| @nicolemoker | 3 | 803 | 446.24 |148.75 |
| 16| @twinkledrop | 5 | 652 | 436.78 |87.36 |
| 17| @sweetsssj | 1 | 1631 | 425.25 |425.25 |
| 18| @wilkinshui | 6 | 1116 | 423.12 |70.52 |
| 19| @rea | 2 | 878 | 418.53 |209.27 |
| 20| @mrspointm | 7 | 925 | 410.55 |58.65 |
| 21| @ace108 | 27 | 2850 | 408.52 |15.13 |
| 22| @someone | 3 | 808 | 405.01 |135.00 |
| 23| @krischy | 3 | 811 | 397.59 |132.53 |
| 24| @kitcat | 5 | 922 | 393.34 |78.67 |
| 25| @aaronli | 4 | 620 | 384.51 |96.13 |
| 26| @sunnyjolly | 7 | 546 | 333.85 |47.69 |
| 27| @jessicameng | 6 | 401 | 319.66 |53.28 |
| 28| @kenchung | 4 | 758 | 318.49 |79.62 |
| 29| @goodboyphilip | 8 | 314 | 306.22 |38.28 |
| 30| @travelgirl | 13 | 1377 | 290.79 |22.37 |

以上收益包括75%的作者收益(Author Rewards)和25%的点赞收益(Curation Rewards)。
@dailystats 提供过去7天全网潜在收益前30名的排名
check @dailystats for top 30 daily authors in the last 7 days sorted by potential payout

CN 区优质内容点赞机器人每天会为这30名作者点赞!
CN Good-Content-Body will upvote these authors every day!

近期机器人点赞记录 Recent Quality-Content-Upvote-Bot Upvoting History

这个机器人很善良,只想激励CN社区产生更多的优质内容。当然别忘记了,我本人人工也是会对未上榜的优质作者进行点赞的哟~
The quality-content-upvoting-bot motivates for more good contents in [CN], however, I will also upvote other authors if good content are produced.

时间 Time 作者 Author 文章 Post 权重 Weighting % VP %
2017-09-23 00:25:16 @ace108 @ace108 15.39 64.12
2017-09-23 00:30:43 @guyverckw @guyverckw 16.64 64.00
2017-09-23 03:12:18 @bring @bring 12.66 63.31
2017-09-23 03:59:19 @zsilence @zsilence 12.76 63.79
2017-09-23 06:07:20 @blackbunny @blackbunny 16.99 65.33
2017-09-23 07:34:14 @tumutanzi @tumutanzi 19.49 64.97
2017-09-23 07:46:03 @deanliu @deanliu 19.55 65.17
2017-09-23 10:49:45 @twinkledrop @twinkledrop 17.31 66.58
2017-09-23 11:04:31 @jubi @jubi 19.96 66.53
2017-09-23 11:10:46 @travelgirl @travelgirl 14.60 66.35
2017-09-23 11:14:13 @someone @someone 13.24 66.19
2017-09-23 12:22:10 @mrspointm @mrspointm 15.67 65.28
2017-09-23 12:51:14 @oflyhigh @oflyhigh 19.42 64.72
2017-09-23 12:51:14 @oflyhigh @oflyhigh 19.42 64.72
2017-09-23 12:52:11 @oflyhigh @oflyhigh 19.42 64.73
2017-09-23 13:34:36 @aaronli @aaronli 14.30 64.98
2017-09-23 14:14:25 @goodboyphilip @goodboyphilip 13.07 65.34
2017-09-23 14:16:49 @krischy @krischy 13.04 65.19
2017-09-23 14:41:40 @wilkinshui @wilkinshui 15.69 65.37
2017-09-23 15:11:14 @htliao @htliao 19.57 65.24
2017-09-23 15:31:37 @linuslee0216 @linuslee0216 18.27 65.26
2017-09-23 15:36:38 @coldhair @coldhair 16.92 65.09
2017-09-23 15:36:38 @coldhair @coldhair 16.92 65.09
2017-09-23 16:10:26 @kitcat @kitcat 15.62 65.08
2017-09-23 16:10:10 @kitcat @kitcat 15.62 65.07
2017-09-23 17:24:13 @rivalhw @rivalhw 18.24 65.14
2017-09-23 23:38:28 @joythewanderer @joythewanderer 14.52 65.98
2017-09-24 00:00:42 @travelgirl @travelgirl 14.54 66.09
2017-09-24 00:18:17 @rea @rea 14.55 66.12
2017-09-24 02:00:41 @nicolemoker @nicolemoker 12.68 63.42
2017-09-24 03:21:32 @ace108 @ace108 14.16 64.36
2017-09-24 03:38:26 @rivalhw @rivalhw 18.03 64.40
2017-09-24 03:38:22 @rivalhw @rivalhw 18.03 64.40
2017-09-24 04:55:39 @deanliu @deanliu 19.46 64.86
2017-09-24 05:16:19 @linuslee0216 @linuslee0216 18.17 64.89
2017-09-24 05:33:24 @mrspointm @mrspointm 14.27 64.88
2017-09-24 09:13:34 @jubi @jubi 18.47 65.97
2017-09-24 10:08:12 @htliao @htliao 17.11 61.11

每日’那些优秀被错过的文章’ 排行榜几点说明:

  • 取的是发贴在3 到4天前的 10个较少收益(较少被关注,但同时质量又较不错)的帖子。
  • 生成的结果去掉了 我自己的帖子 @justyy
  • 有可能会微调参数让结果更合理。
  • 查询SQL语句和参数暂时不公开,因为这样会更公平一些。

这个报告的年龄为: 30天。 i.e. 发布的第一天, 发布的第二天
为什么要有这个报告?
欢迎 @justyy 如果您有好的建议或者想看哪些榜单却找不到。
Tags: #cn

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: Daily #CN Updates - CN社区每日榜单【优质内容机器人点赞记录】【过去7天潜在收益排行榜】【那些优秀被错过的文章】(2017-09-24)

STEEM SQL 系列之 历史上的今天怎么实现的?SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?

Thank you @arcange for creating STEEMSQL!

STEEM SQL Tutorial Series:

@dapeng has some posts on “History of Today” which reveals some old posts that were published at the same time (but not this year apparently) on steemit. It can be achieved using the following SQL.

1
2
3
4
5
6
7
8
9
select top 10 *
from
Comments
where
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
title <> ''
order by
total_payout_value desc

Let’s explain this line by line.

  • select top 10 * Choosing the first 10 posts
  • from Comments Table Comments holds posts and comments
  • FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') The Month and Day should be the same as today
  • FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') But it can’t be this year.
  • title <> '' Filter out comments, which have empty titles.
  • order by total_payout_value desc Show the most earned posts first.

We could also add other conditions, such as categories = 'cn' if we are only interested in the CN posts.


Image Credit: Pixabay.com

感谢 @arcange 创造了 STEEMSQL!

STEEM SQL 系列:

@dapeng 之前搞了一个 “历史上的今天” 挖坟贴 ,也就是通过 SQL 查询 在以前在同一天发表的帖子,听起来很玄乎?实际上就是以下SQL。我们先来看看:

1
2
3
4
5
6
7
8
9
select top 10 *
from
Comments
where
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
title <> ''
order by
total_payout_value desc

我们来解释一下:

  • select top 10 * 选择前10条记录
  • from Comments 查询 STEEMSQL 里的 Comments
  • FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') 发表时间的月和日要和今天一样
  • FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') 但是又不能是今年。
  • title <> '' 标题为空,也就是限制文章类型(评论的标题一般是空)
  • order by total_payout_value desc 按照收益排序

我们还可以其它条件, 比如 categories = 'cn' 只查询第一个标签为 cn 的帖子。

CN 每日排行榜

// Later, it may be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客

Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.

原文首发于 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。

@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:


欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.


This page is synchronized from the post: STEEM SQL 系列之 历史上的今天怎么实现的?SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×