In this post we discuss the data selection and the processing used to calculate the end of month delegation balances since delegations started in March 2017.
Let's see the first month of delegation transactions. This is how the delegation data comes from the SteemSQL TxDelegateVestingShares table (https://steemsql.com/database-diagram/):
| delegator | delegatee | vesting_shares | timestamp |
|---|---|---|---|
| liberosist | dunia | 94,599,167 | 2017-03-30 15:39:21.0 |
| arhag | ah1 | 1,000,000 | 2017-03-30 16:40:54.0 |
| arhag | ah1 | 0 | 2017-03-30 16:46:24.0 |
| obama | clinton | 21,642 | 2017-03-30 18:05:33.0 |
We see that arhag delegated 1 million VESTS to ah1 on March 30, 2107, at 16:40:54, and that a few minutes later arhag canceled all his delegation to ah1. So arhag had no delegation at the end of March 2017. But obama had. He delegated 21,642 VESTS to clinton on March 30, 2017 and did not changed the amount delegated on that month. The same happened to liberosist and dunia so we can easily calculate the March 2017 delegated balance as 94,620,809 VESTS.
Each time two accounts make a delegation transaction it gets registered in the Steem blockchain as the total one account is debtor/creditor of the other one. As a result of this structure, the last transaction date (timestamp) for each pair of delegator and delegatee in each month is associated with the delegator's balance in that month, if there was a transaction between them in the month. If there wasn’t a transaction between them, we have to look for the last transaction between them back in the blockchain history. Let’s try to write an algorithm in R to extract the end of month balances from the history of delegations.
First we select, for each combination of delegator, delegatee, year and month of delegation, the last timestamp:
dvs <- TxDelegateVestingShares[,list( timestamp = max(timestamp)),by=list(delegator,delegatee,year(ts),month(ts))]
Now we get back to the TxDelegateVestingShares table to pick the ‘vesting_shares’ balance of the timestamps selected previously:
setkey(dvs,delegator,delegatee,ts)
setkey(TxDelegateVestingShares,delegator,delegatee,ts)
s <- merge(TxDelegateVestingShares,dvs)
We need to build monthly sequence of dates and initialize the first year, y, and the first month, m:
dts <- unique( s[,list(year,month)] )[order(year,month)]
y <- dts[1,year]
m <- dts[1,month]
Filter delegations by these initial month and year and get the delegation balances at the end of the first month of delegations:
m0 <- s[year == y & month == m,]
bal <- m0[,list(delegator,delegatee,vesting_shares,year,month,ts)]
Follow the annotations embeded in the code below to check what happens in the middle of the loop of dates:
for( i in 2:(nrow(dts)-1) ){
# Forward one month
y <- ifelse(m==12, y+1, y)
m <- ifelse(m==12, 1, m + 1)
# Filter delegations by the dates of the following month
m1 <- s[year == y & month == m,]
# Accounts that had a balance in the first and in the second periods: select only the second period's balances
setkey(m0,delegator,delegatee)
setkey(m1,delegator,delegatee)
n1 <- merge(m0,m1)[,list(delegator,delegatee,vesting_shares=vesting_shares.y,year=year.y,month=month.y,ts=ts.y)]
# Accounts that had a balance before but whose balance did not change in the current month: select the first period balances
n2 <- m0[!m1][,list(delegator,delegatee,vesting_shares,year=y,month=m,ts)]
# Accounts that had no balance before but showed a positive balance in the current month: select the second period balances
n3 <- m1[!m0][,list(delegator,delegatee,vesting_shares,year,month,ts)]
# From the union of these three sets we get the delegation balances of all Steem accounts in the second month
res <- rbind( n1, n2, n3 )
# Stack the result to the monthly balances data.table
bal <- rbind(bal, res)
# res is the new m0
m0 <- res
}
All the monthly delegation balances were stacked in the "bal" data-table. We only need to sum them by year and month to get to the end of month delegation balances time series:
g <- bal[,list(mvs = sum(vesting_shares)/1000000),keyby=list(year,month)]
From the balances we calculate the rate of growth of delegations:
rg <- cbind( g[1:(nrow(g)-1),list(lag=mvs)], g[2:nrow(g),list(mvs)] )
rg[,rg := 100*((mvs/lag)-1)]
Congratulations to those that like calculations!
Flagged because I can't recreate your results. I'm calling BS.
This post has received a 8.21 % upvote from @booster thanks to: @verodato.
You got a 37.20% upvote from @brupvoter courtesy of @verodato!
You got a 7.69% upvote from @upme thanks to @verodato! Send at least 3 SBD or 3 STEEM to get upvote for next round. Delegate STEEM POWER and start earning 100% daily payouts ( no commission ).
You got a 16.28% upvote from @emperorofnaps courtesy of @verodato!
Want to promote your posts too? Send 0.05+ SBD or STEEM to @emperorofnaps to receive a share of a full upvote every 2.4 hours...Then go relax and take a nap!
You got a 21.15% upvote from @dailyupvotes courtesy of @verodato!
Great post!
Thanks for tasting the eden!
You got a 17.89% upvote from @oceanwhale With 35+ Bonus Upvotes courtesy of @verodato! Delegate us Steem Power & get 100%daily rewards Payout! 20 SP, 50, 75, 100, 150, 200, 300, 500,1000 or Fill in any amount of SP Earn 1.25 SBD Per 1000 SP | Discord server
You got a 19.10% upvote from @minnowvotes courtesy of @verodato!
You got a 15.94% upvote from @upmewhale courtesy of @verodato!
Earn 100% earning payout by delegating SP to @upmewhale. Visit http://www.upmewhale.com for details!
This post has received a 7.92 % upvote from @boomerang.
hello sir really thanks for using our bot min is 0.5 but i know you and you filled some our delegation request on minnowbooster so i upvoted you as free is our gift for you sir , if you like you can be part of delegation system we will send 100% sbds which your delegated sp made if you delegate, ,wish the best for you sir
You got a 12.62% upvote from @postpromoter courtesy of @verodato!
Want to promote your posts too? Check out the Steem Bot Tracker website for more info. If you would like to support the development of @postpromoter and the bot tracker please vote for @yabapmatt for witness!
How does this helps in taking investment decision
Knowing the numbers helps find (and justify) where to put the money
Congratulations @verodato! You have completed some achievement on Steemit and have been rewarded with new badge(s) :
Click on the badge to view your Board of Honor.
If you no longer want to receive notifications, reply to this comment with the word
STOPTo support your work, I also upvoted your post!
Do not miss the last post from @steemitboard!
Participate in the SteemitBoard World Cup Contest!
Collect World Cup badges and win free SBD
Support the Gold Sponsors of the contest: @good-karma and @lukestokes
good :)
i think it helps i will make my own research and collect it with this info and i see the final result
MUST SEE THE TREND