Calculating Hive Curation Rewards Using HiveSQL

hivesql.png

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') * 
    (select hive_per_vest from DynamicGlobalProperties),
    ((select SUM(reward) 
        from VOCurationRewards 
        where curator = name 
        and timestamp between '2021-08-01' and '2021-09-01') * 
    (select hive_per_vest from DynamicGlobalProperties)) * 12 /
    ((vesting_shares + received_vesting_shares - delegated_vesting_shares) * 
    (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') * 
    (select hive_per_vest from DynamicGlobalProperties),
    ((select SUM(reward) 
        from VOCurationRewards 
        where curator = name 
        and timestamp between '2021-08-01' and '2021-09-01') * 
    (select hive_per_vest from DynamicGlobalProperties)) * 12 /
    ((vesting_shares + received_vesting_shares - delegated_vesting_shares) * 
    (select hive_per_vest from DynamicGlobalProperties)) * 100
from Accounts
where (vesting_shares + received_vesting_shares - delegated_vesting_shares) * 
    (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 = ''
    count = 1
    for row in data:
        if row[2]:
            row_html = f''
            count += 1
            table += row_html
        else:
            row_html = f''
            count += 1
            table += row_html
    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.

Curation Rewards For August (50K+ HP)

#NameHPCurationAnnual %
{count}{row[0]}{round(row[1],0):,}{round(row[2],0):,}{round(row[3],2)}
{count}{row[0]}{round(row[1],0):,}00
#NameHPCurationAnnual %
1appreciator8,462,09274,56110.57
2ocdb7,365,95565,96710.75
3blocktrades5,810,08647,5869.83
4smooth3,632,24625,1158.30
5rocky12,755,57124,63310.73
6mottler2,475,65000
7buildawhale2,187,83219,90310.92
8blocktrades.com2,144,2361,8401.03
9theycallmedan2,058,46118,19310.61
10curangel1,912,74418,21511.43
11alpha1,770,79814,6629.94
12newsflash1,729,03110,6927.42
13leo.voter1,691,4247,3205.19
14bdvoter1,642,49714,41210.53
15threespeak1,604,86812,1929.12
16xeldal1,459,6298,8627.29
17ecency1,452,9479,8188.11
18ranchorelaxo1,275,6398,7518.23
19trafalgar1,205,00610,52610.48
20darthknight1,180,3618,9429.09
21steemmonsters1,134,4083,1583.34
22singhcapital1,081,5722,1502.38
23tipu1,073,4858,9079.96
24themarkymark1,017,6709,07410.70
25altleft1,014,5746,7908.03
26adm933,7382130.27
27sweetsssj906,3467,4619.88
28tribesteemup900,5297,65310.20
29safari804,31100
30smooth-e802,58800
31gtg721,5756,01710.01
32abit704,1616,54311.15
33enki684,8742,9515.17
34steempty650,6275,2729.72
35steemed-proxy588,1225,39311.00
36usainvote583,7284,95810.19
37ocd554,9494,2179.12
38jphamer1500,7624,47110.71
39recursive495,657450.11
40azircon482,5264,35910.84
41balte482,1113,8899.68
42minnowsupport460,4262,4876.48
43bitcube459,59600
44neoxian456,8663,6789.66
45canadian-coconut441,8553,86210.49
46curie438,5594,01810.99
47v4vapid436,7731,8755.15
48arhag401,13500
49broncnutz390,6842,9859.17
50dbuzz365,9942,8459.33
51ctime361,8093,47111.51
52ramta359,48400
53haejin347,6362,7679.55
54upmewhale343,5703,14310.98
55qurator339,3743,36911.91
56acidyo335,5253,02610.82
57vcelier331,0692,2368.10
58newhope312,7132,80310.75
59coinomite311,8322,59810.00
60riverhead301,76900
61arcange291,8942,56510.54
62dtube288,4051,2185.07
63znnuksfe276,7032,0458.87
64ua-promoter274,9422,61811.43
65alexis555272,3072,43110.71
66trostparadox262,0242,0329.31
67creator255,64700
68roelandp254,5921,5817.45
69mmmmkkkk311251,8232,10210.02
70onealfa235,4572,01610.27
71actifit234,6151,9149.79
72healthsquared232,2912,17811.25
73pfunk230,8002,22611.58
74dhenz224,43200
75edicted221,7341,7859.66
76brofi218,1851,1746.46
77cervantes217,4161,7719.78
78stoodkev216,8861,7749.81
79nautilus-up215,6652,09411.65
80life-timer215,5462,05111.42
81fatimajunio210,8921,83610.45
82project.hope209,9481,7399.94
83ssg-community209,3621,6539.48
84tarazkp203,8882,10112.36
85nbs.gmbh201,8941,5028.93
86waivio.match199,6741,2847.72
87redes198,4521,77310.72
88arpolkin197,3871,5059.15
89gringalicious194,0571,84511.41
90vancouverdining193,554450.28
91ripperone192,1461,71310.70
92hive.curation189,6571,71310.84
93therealyme189,5421,71410.85
94sean-king186,18700
95rima11180,2491,60310.67
96done178,9871,59510.69
97pdq177,9301,4749.94
98stayoutoftherz176,1211,60210.91
99ezrider173,9641,53410.58
100zuerich172,3031,58611.04
101slobberchops169,8821,56811.07
102minnowbooster169,4771,3179.33
103cervantes.one168,3531,3649.72
104steembasicincome168,2781,55411.08
105pharesim165,0522661.93
106hivegc164,7711,51111.01
107ph-support164,4252,03814.88
108dhimmel163,4851,1598.51
109spt-skillup160,7662,25316.82
110fuerza-hispana158,9681,42910.78
111spectrumecons158,9391,36410.30
112extrospect157,9515023.82
113vortac157,7861,0758.17
114steemstem157,4442081.58
115lazy-panda157,13400
116gunthertopp156,1661,35210.39
117smartsteem155,3381,43611.09
118birdwatcher150,4401,39311.11
119peakd150,0516325.05
120node1149,23500
121geneeverett148,7671,2129.77
122foxon147,8341,31910.70
123pishio147,5259667.85
124hanshotfirst145,6942962.44
125diggndeeper.com145,4281,26410.43
126hbdstabilizer143,53600
127mangos137,9191,24610.84
128holiday136,60200
129brettpullen134,3166896.15
130wackou130,99420.02
131oflyhigh130,4851,29411.90
132s4s130,4041,0479.64
133daveks128,1261,17010.96
134zioland127,52300
135laruche126,6751,13910.79
136investegg126,6329739.22
137bitcointalker125,47100
138moderator124,69800
139coininstant121,8251,02410.09
140postpromoter120,2101,04710.45
141pouchon120,0788798.79
142gleam-of-light118,9041,02310.33
143natsu117,99200
144ace108117,7668338.49
145sepracore116,9859729.97
146wagginston116,527420.43
147aggroed113,353100.10
148kommienezuspadt113,0367037.46
149nateaguila112,9811,19012.64
150samantha-w112,91500
151abh12345112,2671,25213.38
152penguinpablo111,8051,21813.07
153msp-curation110,8574274.62
154trumpman110,13594210.27
155msp-waves109,3352292.52
156node2108,44200
157eturnerx108,2618739.68
158hiro-hive107,78698410.95
159gopaxkr107,70500
160cornucopia106,70600
161therising106,59197911.02
162smasssh105,83594410.71
163isaria101,68287710.35
164deanliu101,39893111.02
165traf101,35288310.45
166biggest101,34200
167sbi2100,72987810.45
168dcityrewards100,24598311.77
169c0ff33a99,63888810.69
170bcc99,6287979.60
171solominer99,61988310.64
172btu99,0908259.99
173yabapmatt99,0293093.74
174howo98,11084810.37
175galenkp97,83097511.96
176vincentcestone97,44800
177sandymeyer96,80881510.11
178b0y2k96,2571041.30
179mbp95,8567709.64
180magicmonk94,76884410.69
181shaka94,43379110.06
182eddiespino94,43200.00
183jim88893,38381110.42
184hive-11777892,77478910.21
185goblinknackers92,45181310.56
186ozchartart91,56700
187sbi390,32382811.00
188fedesox90,0923,46346.12
189discovery-it88,6993775.10
190eonwarped88,09179910.89
191steemcleaners88,06680110.92
192unpopular87,74679010.81
193blewitt86,7112403.32
194someguy12385,6045868.21
195pinmapple84,4736208.80
196quochuy84,3935327.57
197gengua82,6996339.18
198engrave81,65974110.88
199condeas81,5326789.98
200holger8080,546731.08
201schlees80,02267110.07
202gardenofeden79,83900
203promoted79,71700
204dynamicsteemians78,4721742.66
205creativeblue78,35668110.43
206honey-swap77,41200
207geoffrey77,08400
208spanishgirl377,08100
209msp-mods76,9451432.23
210buggedout76,4726199.72
211ocd-witness75,8892564.04
212rivalhw75,6555889.32
213cornerstone75,2574987.94
214kevinwong75,09394415.09
215dragonslayer10975,02462810.05
216meesterboom74,47267410.86
217hive.pizza74,1723555.74
218votame74,0651903.08
219intrepidphotos72,42662410.35
220mustard71,944250.41
221netaterra71,82465110.87
222aliento71,5171,08018.13
223anyx70,87200
224taskmaster445070,4845659.62
225jesta70,23800
226solarwarrior70,10564911.11
227rawutah69,8124798.24
228nonameslefttouse69,5455088.77
229luigi69,17000
230node369,16800
231exhaust69,0935699.89
232daltono68,79565811.48
233google67,56100
234gregory-f67,15581614.58
235dsc-r2cornell67,1175459.74
236larryphang67,02730.05
237inertia66,977631.13
238publicworker66,63300
239smartvote66,4403666.60
240talentclub66,0951,17321.30
241sanjeevm66,02367712.31
242algeorge65,01200
243louis8864,5225049.38
244jongolson64,36464311.98
245johannpiber63,30755110.44
246tobetada62,78058311.14
247enable61,93500
248cibersk861,52952310.21
249delishtreats61,17955410.88
250staticinstance61,12900
251hodlcommunity60,2855019.98
252asgarth60,01750310.06
253noaommerrr59,93300
254captainhive59,48251510.39
255jl77758,93400
256jedigeiss58,6584499.19
257bagholders58,3154549.33
258tombstone58,2223527.25
259socky57,33500
260ecotrain56,8373357.08
261ben156,81200
262steevc56,36349910.63
263lunaticpandora56,34252811.25
264lemouth56,094962.05
265administrator56,06700
266emrebeyler56,04351110.94
267spaminator55,412691.49
268stephenkendal55,34200
269pgarcgo55,2954479.71
270janusface55,1892806.09
271livinguktaiwan55,05348010.46
272quinneaker54,95800
273joshman54,8632525.50
274infovore54,62520.03
275steemychicken154,40146510.27
276diahla3154,32700
277dan-bn54,12000
278senseicat53,98600
279thealliance53,97150511.23
280visionaer300353,53065514.68
281sbi453,51250811.40
282ctpsb52,85447810.86
283kryptodenno52,6323127.10
284midlet52,3562956.75
285ewkaw51,67545810.64
286therealwolf51,5823648.47
287cryptoandcoffee51,47974217.29
288geekgirl51,46145410.58
289risingstargame51,45746010.72
290forykw51,20147711.18
291dlux-io50,956641.51
292thecryptodrive50,679701.65
293gooddream50,592761.79
294woo773950,08100
295cannonwar50,05100
H2
H3
H4
3 columns
2 columns
1 column
16 Comments