Building delegation time series

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!