SteemSQL Tutorial – Get Most Single Payout Authors

@nationalpark in his post has listed the top authors that publish exactly 1 post and earn the most. This SteemSQL tutorial is going to uncover the magic behind the scene thanks to @arcange ‘s hard work on maintaining SteemSQL.

Let’s say we need to limit to those authors by publishing only 1 post (excluding comments), by using this criteria.

depth = 0

Then, we need to group author and check if the count of the post is one by having count(1) = 1 i.e. we use having on aggregated fields i.e. author

Then, we just need to sort the result in descending order order by max(total_payout_value) desc because it is only 1 post, you can use max, min or sum which does not make any differences.

The final SQL is:

1
2
3
4
5
6
7
8
9
10
11
12
13

select 
    author, 
    max(total_payout_value) "payout"
from
    comments (NOLOCK)       
where
    depth = 0
group by
    author
having
    count(1) = 1
order by
    max(total_payout_value) desc

select 
    author, 
    max(total_payout_value) "payout"
from
    comments (NOLOCK)       
where
    depth = 0
group by
    author
having
    count(1) = 1
order by
    max(total_payout_value) desc

as you can see max(total_payout_value) is repeated twice, and unlike MySQL, we can’t customize this field and use it as a variable e.g. payout, However we can do a nested SQL, and rewrite the above:

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

select T.author, T.payout 
from (
    select 
        author, 
        max(total_payout_value) "payout", 
        count(1) "count"
    from
        comments (NOLOCK)       
    where
        depth = 0
    group by
        author
) T
where
    T.count = 1
order by
    T.payout desc

select T.author, T.payout 
from (
    select 
        author, 
        max(total_payout_value) "payout", 
        count(1) "count"
    from
        comments (NOLOCK)       
    where
        depth = 0
    group by
        author
) T
where
    T.count = 1
order by
    T.payout desc

Both queries return the same result:

steemsql-authors-payout-one-post

Confirmed with @arcange, the earnings are SBD not USD, so the figures are slightly different than what you read in @nationalpark ‘s post.

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?

You may also like: SteemSQL 教程 – 一鸣惊人的作者

–EOF (The Ultimate Computing & Technology Blog) —