Calculating Hive Curation Rewards Using HiveSQL

in LeoFinance2 months ago

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 = '<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.

Curation Rewards For August (50K+ HP)

#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

Posted Using LeoFinance Beta

Sort:  

Geez those nested queries :)
There is probably some tiny inconsistency on a yearly basis, because of the vest to hive ratio, that is changing all the time.

Great job!

I wasn't expecting sql pros to see this. :)
I was testing queries as I was writing the post and didn't get a chance optimize it. :)
Normally I would have less nested queries and do more computation in python, I just wanted to make sure these work without python as well.

Good point, I didn't even consider how vest to hive ration changes.

Thank You @dalz!

No wories just trying to contribute to your hard work.... also far from sql pro :)

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.

I wonder what is the reason(s) behind this. As I know the curation time penalty has been deleted by the latest hardfork, so theoretically everyone earn curation rewards, even if you upvote the post/comment 1 minute after posting.

But I also heard about some penalty in the amount of curation rewards, when upvoting posts/comments older than 1 day. As I heard, this has been introduced by the latest hardfork. I do not know if it is true.

That is a good point. I don't know the answer to that either. I also thought that if voted first day the curation rewards should be proportional to the voting weight. Not sure.

I really need to look deeper into what HiveSQL can do. I have had some fun with it.

It's interesting to see how many large accounts are not voting at all. It looks like I am about average for curation income. I am not too strategic about how I vote.

!PIZZA

Yes, HiveSQL is fun.

I did notice that too, how some large stakeholders are not active in curating at all. Win for rest of us I guess. :)

I don't follow any strategy either when curating. I think just voting what see and like is the best way. Rewards are just a bonus.

Thank You!

@geekgirl! I sent you a slice of $PIZZA on behalf of @steevc.

Learn more about $PIZZA Token at hive.pizza (2/10)

THis looks like a excellent explaination of coding done...not a coder but it looked interesting, the logic that you explained in the code.

Nice that you can calculate curation rewards for the month like this...smart and cool stuff!!

This is good work done dear. Keep it up. We pick inspiration for this

Thank you.

Wow, lots of data there - and I was surprised to see my curation rewards at 12.31 % . I usually use https://hivestats.io/ to see rewards sometimes.

Now I undersatnd some of this, the overall concept looks amazing and shows what can be achieved. Thanks for sharing this

I am non-technical but seems HiveSQL is something that can fetch interesting stats. is there any possibility to learn it for people like me who come from a non-technical background?


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 https://hiveposh.com.

BOOM1.jpg

toruk_washere_new3leo.jpg

Read how this all have started with Toruk

Posted Using LeoFinance Beta