HiveSQL: Author Rewards & Curation Rewards

in LeoFinancelast year (edited)


If anything Hive blockchain is good at, that would be rewarding authors and curators. Author and curation rewards in Hive's DNA. Sometimes it may be fun and useful to get blockchain data regarding these rewards. The best way of retrieving such data is using HiveSQL. You may have read my post on HiveSQL and how awesome it is. Let me demonstrate once more the super powers of HiveSQL.

This time as the title suggests we will explore author and curation rewards. HiveSQL makes it easy for us by providing two tables that contain this data: VOAuthorRewards and VOCurationRewards. I assume VO stands for virtual operations. Feel free to correct me if I am wrong.

VOAuthorRewards has the following columns:

  • ID
  • block_num
  • author
  • hbd_payout
  • hive_payout
  • vesting_payout
  • permlink
  • timestamp

As you can see every column is well named to describe what kind of data is stored there. ID usually is a unique identifier for a data entry. I believe block_num represents the Hive block number where this data is stored in Hive blockchain. author is the account name for the author. hbd_payout, hive_payout, and vesting_payout are different type of native assets that author was rewarded with. Normally authors rewards are slit into two: hbd rewards, and hp rewards. Vesting_payout is hive power rewards in vests. In rare situations there are time when Hive blockchain pays liquid portion of the rewards in Hive instead of HBD.

import pymssql
import os
import pprint

def hive_sql(SQLCommand, limit):
    db = os.environ['HIVESQL'].split()
    conn = pymssql.connect(server=db[0], user=db[1], password=db[2], database=db[3])
    cursor = conn.cursor()
    result = cursor.fetchmany(limit)
    return result

if __name__ == '__main__':
    SQLCommand = '''
    SELECT *
    FROM VOAuthorRewards
    WHERE author = 'geekgirl'
    and timestamp between '2022-02-01' and '2022-03-01'

    result = hive_sql(SQLCommand, 10)
    print('TASK FINISHED')

Since I make my HiveSQL queries in python, I usually have a template that connects to the database and all I need to do is write the query and assign it to SQLCommand variable, as you can see above.

SQL query itself is only four lines:

    SELECT *
    FROM VOAuthorRewards
    WHERE author = 'geekgirl'
    and timestamp between '2022-02-01' and '2022-03-01'

This will return my author's reward transactions for February 2022. We don't have to limit the results to only one account. We can do so for all of the author's reward transactions stored in the Hive blockchain then apply some aggregate functions and come up with more interesting analysis of the data.

Now let's take a look at VOCurationRewards table. It has the following columns:

  • ID
  • block_num
  • author
  • curator
  • permlink
  • reward
  • reward_symbol
  • timestamp

Again, naming is well done, we don't even have to explain what each of them represent. Curation rewards are normally paid in HP or in other words in vests. That's why the reward_symbol will always be 'VESTS'. Rest of the items in the list are self-explanatory. But if you have questions regarding any of these items, feel free to ask in the comments.

If I wanted to get my curation rewards transactions for February, I can use the same exact code above. By changing the table name that comes after 'FROM' and changing author to curator.

    SELECT *
    FROM VOCurationRewards
    WHERE curator = 'geekgirl'
    and timestamp between '2022-02-01' and '2022-03-01'

I know these codes are boring. However, using these two HiveSQL tables we can create much more interesting and useful queries, like the progress of various account throughout their journey on Hive network. We can compare frequency of rewards vs the amount of rewards at various times. Use of aggregate functions takes such data analysis to another level. Have you started using HiveSQL yet? If so what do you use it for?

Posted Using LeoFinance Beta


I can't separate post votes and comment votes. The columns of the table txvotes in hive-sql are arranged this way.


Can you tell me how to separate post votes and comment votes? Do you think that is not possible?

You may have to combine TxVotes table with Comments table using JOIN. Here is an example.

    SELECT, v.permlink, c.title, v.timestamp
    FROM Comments c
    JOIN TxVotes v
    ON c.permlink = v.permlink
    WHERE v.voter = 'geekgirl'
    AND =
    AND c.parent_author = ''
    AND v.timestamp BETWEEN '2022-02-01' AND '2022-03-01'
    ORDER BY v.timestamp ASC

If parent_author is empty or nothing, it would mean it is a post, otherwise it is a comment.

You should not use parent_author='' to differentiate posts from comments but depth=0!
The depth column has its own index and numeric value comparisons are way faster than string comparisons, especially on a big number of rows.

It's also good practice to explicitly specify which JOIN type you use and to have all join conditions in the JOIN clause rather than splitting them into the WHERE clause.

Finally, you are making your JOIN in the wrong order. The main table should be TxVotes (table in which you are looking for info) and Comments should come next as it's a table you use to filter and get alternate data.

    INNER JOIN Comments ON 
        Comments.Author = TxVotes.Author
        AND Comments.permlink = TxVotes.permlink
    TxVotes.voter = 'geekgirl'
    AND Comments.depth = 0
    AND TxVotes.timestamp BETWEEN '2022-02-01' AND '2022-03-01'
    TxVotes.timestamp ASC

Thank you Arcange! This is super helpful.

@topbooster, see above comment by Arcange for a correct answer to your question.

This is great @arcange , I would be more benefited if you elaborate on what the depth column indicates and how to separate the only comment votes data.

depth is the depth level in the comments tree, where depth=0 indicates a root post and depth > 0 are comments

Thank you and @geekgirl for the help.

Thank you @geekgirl, your work is valuable

I still haven't used HiveSQl and there is no reason to refuse the basic queries. Every complex query can be broken down into different parts and it's just a natural part of the process.

Posted Using LeoFinance Beta

Perhaps off-topic, how to get the best curation reward on the HIVE blockchain?

It appears that hiveSQL is very helpful in the chain. Its a pity that some of us are not knowledgeable in the coding aspect, sending command, and creating queries. 😥😥😥

yup, hive is so good. Its great to know that all the HIVE and HBD moving around is so nicely and automatically handled for us.

Kudos bruh,indeed knowledge is power. Not only knowing it, u applied it and it, thanks for the information

I have a question for you. I have a comp sci degree and some years of experience, but I graduated back in 2000. It is surprising how many of my skills are still relevant! Anyhow, I recently got fascinated by the blockchain and would like to reboot my programming skills.

My rusty skills are in C/Java/C++/Perl and I was a UNIX system administrator for years. I was familiar, but never an expert with SQL.

So, my question is, if I am interested in getting into blockchain programming, where do you think I should start? What language would you start with if you were me? Are there instructional sites you might recommend? I usually teach myself, but I am open to ideas.

I thought I would begin by learning Python, but maybe there is a better starting point?

Thank you for any answer you have time to provide. I totally understand if you are too busy to reply.

(Do I have a slice of pizza left? I keep getting muddled. I will try:)


I believe using the hiveSQL will be really helpful in the blockchain

The rewards earned on this comment will go directly to the person sharing the post on Twitter as long as they are registered with @poshtoken. Sign up at

By all means, please make more of these. Thank you!

Nice one. I Never knew of Hive SQL until today. It's more like programming I guess

This post has been manually curated by @bhattg from Indiaunited community. Join us on our Discord Server.

Do you know that you can earn a passive income by delegating your Leo power to @india-leo account? We share 100 % of the curation rewards with the delegators.

Please contribute to the community by upvoting this comment and posts made by @indiaunited.

Your content has been voted as a part of Encouragement program. Keep up the good work!

Use Ecency daily to boost your growth on platform!

Support Ecency
Vote for new Proposal
Delegate HP and earn more

Yes ooo, the rewards is really good and impressed me so much


PIZZA Holders sent $PIZZA tips in this post's comments:
@harlowjourney(1/5) tipped @geekgirl (x1)

You can now send $PIZZA tips in Discord via!