Posts

Calculating Hive Curation Rewards Using HiveSQL

avatar of @geekgirl
25
@geekgirl
·
·
0 views
·
5 min read

There probably are already tools that calculate curation rewards out there. But it is fun to do so programatically. We could get such data directly from the Hive blockchain. Or we could also use HiveSQL, which is easier and faster.

You may want to know your monthly curation rewards to see the returns on your HP investment, or to see how much other HP investors are earning in curation and compare. Such information can help with making better decisions.

For example, some investors can earn rewards on their Hive Power with participating in curation activities or some may choose to buy HBD instead and earn annual 10% interest with low risks on the underlying asset (HBD). I prefer investing in HP. Because it enables more active participation in the Hive network, rewards distribution, and governance. It also has no limits on how high the price of the underlying asset (Hive) can go over longer period of time, while also having a risk of going down in price significantly.

Let's assume price of Hive doesn't change within a year, can Hive Power return similar earning like HBD. Can HP earn 10% a year in curation rewards? Let's find out with writing some code and getting this information using HiveSQL.

I personally prefer to use HiveSQL query commands within a python code which helps automating and reusing the code for other projects. But you can use any other SQL app or tool to connect to the HiveSQL database.

To start I use my simple python script template that has a function to connect to HiveSQL:

import os 
import pymssql 
 
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 

hive_sql function receives SQLCommand as the first argument which contains SQL query code. Second parameter limit is there to set the limit of the results received back from HiveSQL.

I am using os.environ[] to store the private keys that are provided by HiveSQL to connect to the database. If the code is only used locally and not publicly accessible, there is no need for this. I used to just put in the keys within the script, in the past. Now I use this method, so I can upload files on github and/or use them within heroku apps without worrying that private keys being exposed.

Before we try to get the curation rewards let's connect to HiveSQL and get a global variable that helps with converting vests to hive. We will need it later. Because HP and curation rewards are represented in vests on Hive blockchain, we need this extra step.

SQLCommand = ''' 
SELECT hive_per_vest 
FROM DynamicGlobalProperties 
''' 
hpv = hive_sql(SQLCommand,1)[0][0] 

Now let's see how much HP I have. If we making this sql query within python we can just reuse hpv variable within the sql code as following:

SQLCommand = f''' 
select name, 
    (vesting_shares + received_vesting_shares - delegated_vesting_shares) * {hpv} 
from Accounts 
where name = 'geekgirl' 
''' 
hp = hive_sql(SQLCommand, 1) 
print(hp) 

Alternatively, we can include a subquery within our sql command to get the hive_per_vest value:

SQLCommand = ''' 
select name, 
    (vesting_shares + received_vesting_shares - delegated_vesting_shares) *  
    (select hive_per_vest from DynamicGlobalProperties) 
from Accounts 
where name = 'geekgirl' 
''' 
hp = hive_sql(SQLCommand, 1) 
print(hp) 

Second option is probably better, since it can be used in other sql query apps and we don't have to make two separate queries.

Accounts table in HiveSQL has rows/values like vesting_share (owned hp), received_vesting_shares (receive hp delegation), delegated_vesting_shares (hp delegated away). We need all three values to calculate the current hp of the account.

There is one more useful row/value we can use - vesting_withdraw_rate. It helps us to see the power down rate, which lowers the hp. For simplicity I am not going to use it here.

Now that we know the current hp, let's get the curation rewards for the month of August.

SQLCommand = ''' 
select SUM(reward) * 
       (select hive_per_vest from DynamicGlobalProperties) 
from VOCurationRewards  
where curator = 'geekgirl'  
and timestamp between '2021-08-01' and '2021-09-01' 
''' 
curation_rewards = hive_sql(SQLCommand, 1) 
print(curation_rewards) 

VOCurationRewards table has rows/values like reward, curator and timestamp among others. These should be enough for us to get the sum of rewards for the months of August.

Using hp and curation rewards results we can calculate what the annual earnings are. We could also get the curations rewards for the whole year, instead of one month. I prefer to use only one month, because HP changes over time; users can power up or power down, delegate or undelegate. One year is a long time to assume that account had maintained the current amount of HP thought the entire year. Even basing the calculation based on one month many not be 100% accurate. But I think reasonable enough to get approximations.

Alternatively, we can combine the two queries into one and get as result: name, current HP, previous month's curation rewards, and approximate annual earnings.

SQLCommand = ''' 
select name,  
    (vesting_shares + received_vesting_shares - delegated_vesting_shares) *  
    (select hive_per_vest from DynamicGlobalProperties), 
    (select SUM(reward)  
        from VOCurationRewards  
        where curator = name  
        and timestamp between '2021-08-01' and '2021-09-01') <em>  
    (select hive_per_vest from DynamicGlobalProperties), 
    ((select SUM(reward)  
        from VOCurationRewards  
        where curator = name  
        and timestamp between '2021-08-01' and '2021-09-01') </em>  
    (select hive_per_vest from DynamicGlobalProperties)) <em> 12 / 
    ((vesting_shares + received_vesting_shares - delegated_vesting_shares) </em>  
    (select hive_per_vest from DynamicGlobalProperties)) * 100 
from Accounts 
where name = 'geekgirl' 
''' 
result = hive_sql(SQLCommand, 1) 
print(result) 

This code is now getting a little bit messy and using too many subqueries. If you know a better way, feel free to let me know in the comments. But it is using the same logic as we have been following so far.

The results show: I have 51,459 HP, curation rewards for August were 454 HP, and this resulted in approximately 10.5% annual earnings.

Now that I can calculate curation rewards for one account, I should be able to do the same for multiple accounts and compare the results.

SQLCommand = ''' 
select name,  
    (vesting_shares + received_vesting_shares - delegated_vesting_shares) *  
    (select hive_per_vest from DynamicGlobalProperties), 
    (select SUM(reward)  
        from VOCurationRewards  
        where curator = name  
        and timestamp between '2021-08-01' and '2021-09-01') <em>  
    (select hive_per_vest from DynamicGlobalProperties), 
    ((select SUM(reward)  
        from VOCurationRewards  
        where curator = name  
        and timestamp between '2021-08-01' and '2021-09-01') </em>  
    (select hive_per_vest from DynamicGlobalProperties)) <em> 12 / 
    ((vesting_shares + received_vesting_shares - delegated_vesting_shares) </em>  
    (select hive_per_vest from DynamicGlobalProperties)) <em> 100 
from Accounts 
where (vesting_shares + received_vesting_shares - delegated_vesting_shares) </em>  
    (select hive_per_vest from DynamicGlobalProperties) > 50000  
order by (vesting_shares + received_vesting_shares - delegated_vesting_shares) desc 
''' 
result = hive_sql(SQLCommand, 1000) 
table = buildTable(result) 
print(table) 

In the code above we are making a query to get name, hp, curation rewards for August, and annual percentage estimation for all accounts that have more than 50k HP.

You may have noticed buildTable function. It is a helper function that formats the results so that I can display the results in a table format.

def buildTable(data): 
    table = '<table><tr><th>#</th><th>Name</th><th>HP</th><th>Curation</th><th>Annual %</th></tr>' 
    count = 1 
    for row in data: 
        if row[2]: 
            row_html = f'<tr><td>{count}</td><td>{row[0]}</td><td>{round(row[1],0):,}</td><td>{round(row[2],0):,}</td><td>{round(row[3],2)}</td></tr>' 
            count += 1 
            table += row_html 
        else: 
            row_html = f'<tr><td>{count}</td><td>{row[0]}</td><td>{round(row[1],0):,}</td><td>0</td><td>0</td></tr>' 
            count += 1 
            table += row_html 
    table += '</table>' 
    return table 

When we run the script we get the following results. You will see how very few accounts can earn more than 15% in curation, some less than 10%, and some don't get curation rewards at all.

Posted Using LeoFinance Beta