Steem blockchain: the forest and the trees

There’s some very good services to get information about individual accounts in the Steem blockchain (steemit.com, busy.org, steemd.com, and others listed in http://steemtools.com/) but it is more difficult to get Steem aggregated data, like the total Steem Power delegation across all Steem accounts. Or tracing how the total amount of SP delegated has evolved since it started.

I found recently this interesting account @penguinpablo via @reko. I’ve learned a lot with @paulag but these pages are not interfaces to play with the blockchain data. In the past there was SteemData, a services that was discontinued (https://steemit.com/steemdata/@furion/steemdata-is-shutting-down-on-may-1st). So it is easy to examine a tree, or Steem account, but it is difficult to get a picture of the forest, that is, the blockchain aggregated data.

A solution that I have adopted and that I’m going to use throughout these posts is the SteemSQL service (steemsql.com). It is a Microsoft SQL Server in the cloud with all the Steem blockchain data synchronized, stored in a convenient collection of tables, and easily accessible. The bad news is that this amazing service is paid.

The database tables, shown in the figure above, and their descriptions are here:

https://steemsql.com/database-diagram/

We’re going initially to focus on the “Accounts” table, where we can find the VESTS delegated by each account and the total VESTS each account has. The “Accounts” table reveals the current state of delegations but we can’t derive from this table (as far as I know) the evolution in time of delegations. To do it we need the “TxDelegateVestingShares”. But, before we can analyze the data, we need to connect to the database. In the next post we’ll discuss how to do it using the R software.