Financial breakdown of the steem blockchain - Steem allocation and Voting Use

in #utopian-io7 years ago (edited)

In this study I revisit the financial breakdown of the Steem blockchain that I first derived back at the end of November 2017.

The breakdown considers:

  • How much of the Steem currency is held as Steem Power and thus eligible for voting on reward allocation and how much is held as Liquid Steem or Steem in Savings.
  • What proportion of the powered up Steem is actually used for voting and how much is sitting idle or not fully utilised.
  • How the above measures vary by user level (minnow, dolphin, orca, whale etc).
  • How this position compares to the breakdown derived at the end of November.

The aims of the study are:

  • To aid Steem account holders, potential investors and developers in understanding where the Steem currency is held and the extent to which it is being utilised for reward allocation.
  • To look at the change of this position over time to give an indication of whether the rise in SBD and Steem prices has impacted the choice of holding Steem Power, or its use in voting.

SteemPowerUsage.png

The snapshot of the currency breakdown has been taken at the end of the 18th January 2018. The analysis of voting use covers the past week; 12th - 18th January 2018. The previous analysis was also for one week; from 22nd - 29th November 2017.

There are two parts to the analysis:

  • How much Steem is held as Steem Power:
    A breakdown of the Steem currency between Steem Power, Liquid Steem and Steem in Savings with a further categorisation by user level.
    This breakdown is then compared to the position at the end of November.

  • What proportion of Steem Power is used for voting:
    An analysis of voting over one week to determine what proportion of Steem Power is used for voting, separated by upvotes and downvotes, and again categorised by user level.
    The position is also compared to the equivalent figures at the end of November.


Outline

  1. Scope of Analysis
  2. Tools Used
  3. How much Steem is held as Steem Power
  4. What proportion of Steem Power is used for voting
  5. Scripts


1 Scope of Analysis

The data has been obtained through SQL queries of SteemSQL, a publicly available Microsoft SQL database containing all the Steem blockchain data.

The amounts of Steem held as Steem Power, Liquid Steem and Steem in Savings can be seen in each user’s wallet. This is even the case for the overall amounts of Liquid Steem held on the currency exchanges. This first part of the analysis requires the data for all Steem accounts. A grouped summary has been extracted from the Accounts table.

The voting behaviour has been determined using data from the TxVotes table. Only data for accounts voting over the last week has been extracted. The data has been filtered by date using the timestamps for each vote in the TxVotes table.


2 Tools Used

Valentina Studio, a free data management tool, was used to run the SQL queries. The raw data was then verified and analysed in the spreadsheet application of the LibreOffice office suite.

Illustrations were produced manually using GIMP, the open source image editor.

SQL scripts are included at the end of this analysis.


3 How much Steem is held as Steem Power

Most users will be aware that not all Steem is Powered Up into Steem Power. There are large allocations of Steem that are thus not eligible for voting. These amounts can be seen in each user’s wallet, either held as immediately transferable tokens (often called Liquid Steem) or more securely in Steem Savings.

The table below shows the breakdown of the Steem currency between Steem Power, Liquid Steem and Steem in Savings with a further categorisation by user level.

Breakdown of Steem Currency
Figures derived as at end of 18-01-2018

Steem PowerLiquid SteemSteem in SavingsTotal Steem
Redfish11,233,82110,730,016776,18522,740,022
Minnows9,420,31145,046,370223,93654,690,617
Dolphins20,710,3941,167,67033,20621,911,271
Orcas37,611,8372,475,6284,74840,092,212
Whales46,971,3712,468,937150,25149,590,559
Steemit Account65,963,1930065,963,193
TOTAL191,910,92761,888,6211,188,325254,987,873
75.3%24.3%0.5%100.0%

A few points to note:

  • ”Steemit Account” refers to the single user account @steemit

  • The user classification is based on Steem Power, which means that investor accounts that hold Liquid Steem but no Steem Power, as well as the exchange accounts such as Bittrex and Poloniex, get classified as minnows, or redfish despite their huge holdings of Liquid Steem.

As can be seen, almost a quarter of the Steem currency is held as Liquid Steem. This is mainly on cryptocurrency exchanges: 27m is held by Bittrex and 15m by Poloniex. The remainder is most likely in the accounts of investors who are not otherwise participating in the Steem environment but do not want their Steem held on an exchange. The amount of Steem held in Savings is now small.

How does this compare to the position of the previous analysis carried out at end November?

Breakdown of Steem Currency
Figures derived as at end of 29-11-2017

Steem PowerLiquid SteemSteem in SavingsTotal Steem
Redfish9,811,64810,817,879720,10321,349,631
Minnows8,301,19740,848,354225,57549,375,126
Dolphins19,104,9501,469,06054,76620,628,776
Orcas36,236,0795,280,34971,65141,588,080
Whales43,969,4811,969,959180,01446,119,454
Steemit Account71,296,69243,372,91674,669,612
TOTAL188,720,04760,385,6074,625,025253,730,679
74.4%23.8%1.8%100.0%

  • Overall the position is broadly similar. The amount of Steem held as Steem Power has increased from 74.4% to 75.3%, an amount of just over 3m Steem but not a huge movement overall.
  • There has been an increase in Steem Power at all user levels (except for the @steemit account which has begun to power down). This could be due to an increase in new accounts at lower levels, a gradual accumulation of power by smaller accounts, or increased delegation from larger accounts. A separate study would be required to fully investigate this topic.
  • A brief examination of the flow of currency in the @steemit account power down shows that a large proportion has been powered back up into the account of @misterdelegation, the account used to support various Steem blockchain projects.
  • The amount of Liquid Steem on the two main exchanges has increased with a shift towards Bittrex. The previous figures were Bittrex 20m and Poloniex 19m.
  • The amount of Steem in Savings has reduced substantially due to the Steemit account liquidation of their holding.


4 What proportion of Steem Power is used for voting

Whilst the overall pattern of Steem Power and Liquid Steem held has not changed significantly, has there been a change in the amount of Steem Power used to vote?

Figures derived for week of 12th - 18th January 2018

Steem PowerSP-up vote %SP-down vote %
Redfish11,233,82112%-0%
Minnows9,420,31149%-0%
Dolphins20,710,39467%-1%
Orcas37,611,83765%-5%
Whales46,971,37142%-2%
Steemit Account65,963,1930%0%
TOTAL191,910,92733%-1.5%

The table below shows that only around 33% of available Steem Power was actually used for upvoting in the week analysed. This equates to approximately 25% of total circulating Steem. Around 1.5% of available Steem Power was used to return rewards to the reward pool through downvoting.

Figures derived for week of 23rd - 29th November 2017

Steem PowerSP-up vote %SP-down vote %
Redfish9,811,64813%-0%
Minnows8,301,19754%-2%
Dolphins19,104,95073%-2%
Orcas36,236,07978%-3%
Whales43,969,48143%-3%
Steemit Account71,296,6920%0%
TOTAL188,720,04735%-1.5%

The figures show that, despite the very significant increases in rewards available in January with the higher SBD and Steem prices, the amount of Steem Power used for voting has actually decreased slightly, from 35% (26% overall) to 33% (25% overall). This is really quite surprising.

It is also noticeable that, despite the current spate of downvoting on the platform, the overall level of power used to return rewards to the reward pool is broadly unchanged.

Based on the above we can update the visualisation of the breakdown as follows:

steem breakdown - Jan 18.png

Conclusions:

  • Despite the significant changes in the Steem environment with the increased level of rewards available, the overall financial breakdown of the Steem currency and level of voting are both broadly unchanged.
  • Whilst the increase in the percentage of Steem currency Powered-Up was slight, the overall percentage - 75% - remains at a high level, illustrating significant commitment to the blockchain from users.

As in the November study, it remains true that approximately 25% of circulating Steem determines the allocation of rewards. Each vote is therefore worth around 4 times its potential theoretical value if all Steem participated in reward allocation.


5 Scripts

This was the SQL query used to generate the Accounts data per user from the Accounts table:


SELECT

    Accounts.name AS [AccountName],
    max(convert(decimal(20,0), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5))) as [VestingShares],
    max(convert(decimal(20,0), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5))) as [ReceivedShares],
    min(-convert(decimal(20,0), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5))) as [DelegatedShares],
    max(convert(decimal(20,0), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5))) as [Vests],
    max(convert(integer,Accounts.voting_power)) as [VotingPower]



FROM
    Accounts (NOLOCK)
        INNER join TxVotes (NOLOCK)
            ON TxVotes.voter = Accounts.name  


WHERE
 
    convert(date, TxVotes.timestamp) >= ‘2018-01-12’ AND
    convert(date, TxVotes.timestamp) <= ‘2018-01-18’


GROUP BY
   Accounts.name

The TxVotes SQL query was similar to the above (same WHERE and GROUP BY clauses) but extracted data from the TxVotes table. It did not require the join to the Accounts table.

This was the SQL query used to generate the grouped summary from the Accounts table:


SELECT

Round(log10(IIF(convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)) < 1, 1, convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)))),0,1) AS [FISH],
    
Count(Accounts.name) AS [NumberOFAccounts],
Sum(convert(decimal(20,4), left(Accounts.balance, len(Accounts.balance)-5))) AS [Steem],
Sum(convert(decimal(20,4), left(Accounts.savings_balance, len(Accounts.savings_balance)-5))) AS [SteemBalance],
Sum(convert(decimal(20,4), left(Accounts.sbd_balance, len(Accounts.sbd_balance)-3))) AS [SBDBalance],
Sum(convert(decimal(20,4), left(Accounts.savings_balance, len(Accounts.savings_balance)-5))) AS [SavingsBalance],
Sum(convert(decimal(20,4), left(Accounts.savings_sbd_balance, len(Accounts.savings_sbd_balance)-3))) AS [SavingsSBDBalance],
Sum(convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5))) AS [vesting_shares],
Sum(convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(delegated_vesting_shares)-5))) AS [delegated_vesting_shares],
Sum(convert(decimal(20,4), left(Accounts.received_vesting_shares, len(received_vesting_shares)-5))) AS [received_vesting_shares],
Sum(convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5))) AS [VESTS]


FROM
    Accounts (NOLOCK)


GROUP BY
Round(log10(IIF(convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)) < 1, 1, convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)))),0,1) 


ORDER by
Round(log10(IIF(convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)) < 1, 1, convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) - convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5)) + convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)))),0,1) 

That's all for today. Thanks for reading!



Posted on Utopian.io - Rewarding Open Source Contributors

Sort:  

Hey, sorry, I was a bit short on time to leave a meaningful comment with the approval earlier.
Great work again! You made me recognize that there is currently around a third of all SP held by steemit, more than all whales together. On the other hand it's not "used". Is this good or bad? I don't know...
Do you have earlier data on the steemit account? Seems like it "lost" around 8% of it's SP since November.

Hey @crokkon.

I think the steemit account was always meant to release their holdings slowly over time to support the blockchain, although I never read anything on the method that was to be followed to achieve this. The fact that the power is not currently "used" to me is both a plus and a minus:

  • If it were all employed at once by a single user it would overwhelm everything else. That user would have huge power, (almost) enough power in fact to vote down everyone else and take the entire reward pool for themselves.
  • However some of this huge power could be employed to promote the blockchain initiatives. This could be a huge plus. I think this is the route now being taken.

The reduction in their SP is due to their recent power down. So far it's been rerouted for delegation (I'll write an article about that later probably). It will be interesting to see if this continues to be the approach. But essentially this should increase the overall level of voting, reducing each user's subsidy from steemit. If it's routed to the right projects this could help resolve some of the current issues, like spam, or promote the alternative apps, like DLive etc.

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

Wow, you've obviously gone to a lot of effort to put this all together. Thanks for the insight! I only joined yesterday so I'm still learning about the intricacies of the platform :)

Thank you Nathan, and welcome to the Steem blockchain! If you've got any dev abilities from your computing background I suggest checking out Utopian - it can be a great way to get started!

Ah thanks I might give it a look, I'm not an expert by any means though so I'm not sure I could be of too much use. I did a years web-dev so I know the basics of HTML, CSS, JavaScript.. a little MySQL etc.

Guess I better start doing my part more. Upvoted.

You've chosen the best place to start! ;)

Yeah, your welcome for that fraction of a penny I just gave you🤑

A great post upvoted and resteemed by @raefbelguith

Thank you for the upvote and resteem @raefbelguith. I'm glad you enjoyed!

Some coding involved. Nice work tho. Upvoted

Thank you Joe. And welcome to the Steem blockchain!

Hey @miniature-tiger I am @utopian-io. I have just upvoted you!

Achievements

  • Seems like you contribute quite often. AMAZING!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x