SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?

MSSQL Database

I will start the tutorial series of getting STEEM data via sql.steemdata.com. 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 Server: sql.steemsql.com
UserName: steemit
Password: steemit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

select top 30 
   author, 
   count(author) as cnt, 
   sum(net_votes) as votes, 
   sum(pending_payout_value) as pending_payout_value 
from 
   Comments 
where 
   title <> and  // only posts
   dirty = 'False' and   // not being flagged
   category = 'cn' and      // remove for all
   parent_author = '' and   // only posts
   datediff(hour, created, GETDATE()) between 0 and 7*24   // last 7 days
group by 
   author 
order by 
   pending_payout_value desc

select top 30 
   author, 
   count(author) as cnt, 
   sum(net_votes) as votes, 
   sum(pending_payout_value) as pending_payout_value 
from 
   Comments 
where 
   title <> and  // only posts
   dirty = 'False' and   // not being flagged
   category = 'cn' and      // remove for all
   parent_author = '' and   // only posts
   datediff(hour, created, GETDATE()) between 0 and 7*24   // last 7 days
group by 
   author 
order by 
   pending_payout_value desc

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

linqpad-steem-sql

You may also like: STEEM SQL 系列之 如何获取最近7天 CN 区用户发贴量，点赞数和估计收益值

STEEM SQL Tutorial

• SteemSQL Tutorial: How to Get Random Posts on SteemIt?
• SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?
• SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?
• SteemSQL Tutorial: How to Calculate Monthly Income on STEEMIT?
• SteemSQL Tutorial: Can we Really Recover Deleted Comments/Posts on STEEMIT?
• SteemSQL Tutorial: What are the Outgoing Votes for Big Whales?
• SteemSQL Tutorial: Count Total Interests Sent
• SteemSQL Tutorial: Finding Inactive Steemians that You Follow
• SteemSQL Tutorial: How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?
• SteemSQL Tutorial: How to Get the Most Payout Authors in History?
• SteemSQL Tutorial: Get Most Single Payout Authors
• SteemSQL Tutorial: How to Avoid SQL Injection?
• SteemSQL Tutorial: I have spent 800 SBD (7000+ USD) buying votes!
• SteemSQL Tutorial: How to Check If SteemSQL is Synchronized with Steem Blockchain?

–EOF (The Ultimate Computing & Technology Blog) —