HiveSQL: Author Rewards & Curation Rewards

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()
    cursor.execute(SQLCommand)
    result = cursor.fetchmany(limit)
    conn.close()
    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)
    pprint.pprint(result)
    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?