Building delegation time series

in #cryptocurrency6 years ago

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/):

delegatordelegateevesting_sharestimestamp
liberosistdunia94,599,1672017-03-30 15:39:21.0
arhagah11,000,0002017-03-30 16:40:54.0
arhagah102017-03-30 16:46:24.0
obamaclinton21,6422017-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!

Sort:  

Flagged because I can't recreate your results. I'm calling BS.

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!

To listen to the audio version of this article click on the play image.

Brought to you by @tts. If you find it useful please consider upvoting this reply.

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!