STEEM SQL 系列之 如何获取最近7天 CN 区用户发贴量,点赞数和估计收益值 SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?

in cn •  2 years ago  (edited)

I will start the tutorial series of getting STEEM data via The tool we are using here is LinqPad and today, I am going to show you how to get the list of authors in the last 7 days who have published posts on the first tag 'cn'. The results are sorted by total pending payout.

You can also contact me @justyy if you want to learn a particular SQL but you don't know how to write it, which then may be included in the next posts.

SQL很简单,我认为是 Sexy Query (查询) Language 语言。这个语言很强大,主要用于操作数据库,现在比较流行的有 MSSQL, MYSQL, SQL SERVER, ORACLE 等。

我们用 LinqPad 来查询。这个系列每次会讲一个语句,如果你觉得你想知道,但是不清楚怎么写的,很欢迎告诉我,我将会整理到下一系列。


下载 LinqPad (免费版就够用了)。然后新建数据库连接:数据库地址是 用户名是 steemit 密码是 steemit

这里不重复贴图了,详细可以看 @joythewanderer 的帖子关于如何添加链接

获取最近7天 CN 区用户发贴量,点赞数和估计收益值

新建 SQL 查询语句,输入以下:

select top 30 
   count(author) as cnt, 
   sum(net_votes) as votes, 
   sum(pending_payout_value) as pending_payout_value 
   title<>'' and 
   dirty='False' and 
   category='cn' and 
   parent_author=''  and datediff(hour, created, GETDATE()) between 0 and 7*24 
group by 
order by 
  pending_payout_value desc

Run the SQL to fetch the top 30 authors, using the LinqPad:

  • 这里 top 30 就是取前30个结果
  • 按 估计收益值从大到小排序:order by pending_payout_value desc
  • 限制 CN 社区: category='cn'
  • 好的帖子 e.g 不被踩过的帖子:dirty='False
  • 是主贴(并不是评论)parent_author=''title <> '' 标题不为空,两个条件一结合比较严格。我发现像 @minnowbooster 的回复也是有带标题的。
  • 时间是过去7天:datediff(hour, created, GETDATE()) between 0 and 7*24
  • 把所有按 author 的帖子分组,取数量,点赞数 还有潜在收益。

前三甲: @oflyhigh @rivalhw @tumutanzi,大腿还有地么?

另:我会今晚把这个排名加到我的 每日榜单里,多提提意见。我想弄一个 有心的机器人 (至少 half human, half bot),让你们都爱上我,哈哈。


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

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

// Later, it will be reposted to my blogs:, and 稍后同步到我的中文博客和英文计算机博客


Recent Popular Posts

Tags: #cn #cn-programming #steem-dev #steem-sql #steemit

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

I am voting for you, you too have voted, so good that we can go ahead with one another.

Your posts are very informative but its sad i have to wait once in a while to get them in english. Most of the time i just copy your headlines to google translate to see what it means. Finally after 10 days , I am reading one of your blogs again.

// 稍后同步到我的中文博客和英文计算
Shāo hòu tóngbù dào wǒ de zhōngwén bókè hé yīngwén jì suàn
jī bókè.

// later sync to my Chinese blog and English calculations
Machine blog.

I guess you know what i mean now..Thanks for the language conversions you are doing. Regards,

Thanks for your suggestions. I'll try my best to make my posts bilingual..

Thank you.


  ·  2 years ago (edited)


啥 API? 怎么用的?

  ·  2 years ago (edited)



今天 排名上有你啊


I am Cornholio! You will co-operate with my bunghole!