Welcome to Terradash! Part two

After the Luna Crash in May, Governance in Terra voted in favor to the proposal 1623, making that terra blockchain be renamed as “Terra Clasic” and their native Token Luna ($LUNA) be renamed into Luna Classic ($LUNC), and give birth (fork their project) to a new Terra blockchain with a Luna ($LUNA). Although there are people who call the new Terra blockchain and their Native Token as “2.0”.

Structure of the Terradashboard

We’ll focus first in the Metrics asked, looking at Total Supply of $LUNA and then Vesting Schedule in Terra, after that we’ll look the $LUNA being IBC-ed OUT and the Top 100 Richlist of Users, then we’ll look at Stake Activity in Terra, $LUNA Staked and the Top 100 Richlist for Validators with staked $LUNA and finally the Stacking Rewards distributed to Delegators and Validators Commissions.

Summary

Although most of the Luna activity is concentrated during the Launch and in September with the Increase of the ==$LUNA==, the Governance activity in ==$LUNA== seems pretty healthy with a lot of transactions, but there are some periods where the staking activity becomes lower such as the last days of August, October or nowadays in December, but the last month the Stake Activity of users was really good with a lot of transactions and $LUNA staked volume.

Delegators of staked $LUNA are farming nice rewards in $LUNA, and this volume is a lot larger than the commissions from validators, so it’s seems that although $LUNA price doesn’t present new increases in price, the governance users are happy farming these rewards.

The amount of Staked ==$LUNA== is really high in comparison with the total supply, meanwhile the amount of ==$LUNA== being Bridge by the IBC doesn’t reach the 1% of the Total ==$LUNA== Supply.

Made by Popex 404, Twitter Link Here

🔍Top 100 Richlist of Validators by $LUNA Staked - Analysis 🔍

• 🥇terravaloper188e99yz54744uhr8xjfxmmplhnuw75xea55zfp Validator Have the most ==$LUNA== staked volume and is almost than 10% of the total from the top 100.🥇
• 🥈 terravaloper1lelhxdzwn9ddecv6sv0kcxj5tguurxnzcfs5wf Validator is in the Second Place of ==$LUNA== staked volume having 8.41% of the total.🥈
• 🥉terravaloper1kxyzu575tex8dv6d5uk26t4q8x5lusw082w3t8 Validator is in the Third Place of ==$LUNA== staked volume having 8.1% of the total.🥉

Again this list presents problems with the real data from the Terra scanner, although this time i used all the data from the terra.core.ez_staking it’s seems that there is problems with the data present there, because the third place validator have more Staked ==$LUNA== and voting power, but still with this richlist we can have a general idea of the validators with most Staked $LUNA volume.

🔍 Top 100 Richlist of Users Analysis🔍

• 🥇terra1tygms3xhhs3yv487phx3dw4a95jn7t7l8l07dr User Have the most ==$LUNA== volume and is more than 40% of the total from the top 100.🥇
• 🥈 terra1jrq7xa63a4qgpdgtj70k8yz5p32ps9r7mlj3yr User is in the Second Place of ==$LUNA== volume having 23.7% of the total.🥈
• 🥉terra1jv65s3grqf6v6jl3dp4t6c9t9rk99cd8pm7utl User is in the Third Place of ==$LUNA== volume having 13% of the total.🥉

One of the main problems is that calculate the TVL from a user from this table doesn’t show exactly correct real time values, for example visiting the terra links i used for the users we can see that in reality, the Number 3 is the one with most ==$LUNA== Balance right now, Second place is still in second place, and the number one is in reality the 3rd place.

I tried my best to bring a top 100 richlist and although their values aren’t correct, they’re still in the Richlist with most $LUNA.

Vesting Schedule in $LUNA

Using terra.core.ez_transfers we can see that message_type is a column that have all the info about what type of transfers are, in particular there are 3 with Vesting Information:

/cosmos.vesting.v1beta1.MsgDonateAllVestingTokens -- /cosmos.vesting.v1beta1.MsgCreatePeriodicVestingAccount -- /cosmos.vesting.v1beta1.MsgCreateVestingAccount

Using these message_type we can get all the info about Vesting in Terra.

🔍 Supply of $LUNA Analysis🔍

• During the First days of Terra, the Flow of $LUNA was really high reaching almost 30M of ==$LUNA==, but after some weeks the flow of $LUNA starts to be lower, with some spikes at the end of each month.
• We can see the biggest spike of $LUNA is in September, and that correlates with the day when $LUNA== $USD value increases from $1.9 to $6, in that moment the total flow of $LUNA reached 43M of ==$LUNA.
• The Amount of ==$LUNA== being bridge is correlated with the ==$LUNA Flow and is always in a ratio of 1/100 of the daily ==$Luna Flow==, more details later.
• The Cumulative increase of $LUNA Flow is constant most of the days with the exception of the spikes from Launch and September which presents a big increase.

The Price of $LUNA is the biggest reason for the spikes in the Circulating $LUNA.

Total Supply of $LUNA

This is the Metric most difficult to get correctly, because the best way is doing a complicated work with the terra.core.fact_transactions to split the logs and i don’t have the enough SQL skills to do in that way, so i used the terra.core.ez_transfers table to calculate all $LUNA flow in Terra, first we need to filter by currency = ‘uluna’ and then we can sum(Amount::integer/1e6) to get the total $LUNA Flow, i need to use the integer function because the amount in this table is a ‘String’ and not a numeric value, also i need to provide the Decimal correction with 1e6.

With this i can calculate all the Flow of $LUNA in Terra, and that is my best shot to provide a Total Supply of $LUNA, and with that also i can show the circulating $LUNA using block_timestamp to show the flow of $LUNA over time.

All the Transfers in terra.core.ez_transfers have a transfer_type column but the problem is that there are only IBC_Transfer_In and IBC_Transfer_off Values so i assumed that the first means a IBC Bridge and the Second is not a Bridge (A terra Transaction)

🔍 $LUNA being IBC-ed OUT Analysis🔍

• As explained before the amount of $LUNA flow by IBC and the total $LUNA is in a ratio of 1/100, but that number was too much for the real percentage, which is 0.0982% percent of ==$LUNA== being Bridge vs total $LUNA supply, the Flow of Bridges almost reaches the 1M of ==$LUNA.
• In the daily chart we can see that in May 29 the percentage reaches almost 1.5%, but after that drops again to lower numbers with some spikes in August, but during the $LUNA== price Spike in September it’s seems that the IBC Bridge didn’t have too much $LUNA Flow.

🔍 Vesting Schedule Analysis🔍

• User terra19qjrfluryf8397hjew4prs8ve6wupgyn3338nv created ==2 Periodic Vesting Account== in August 18&19 and their ==$LUNA== (432K) was sended to 2 different users.
• User terra1awhhavn43q4t936yprdfwpe7q0yfk5hshydp8q created a ==Vesting Account== in September 9, with a little amount of ==$LUNA== (0.1)
• User terra1jv65s3grqf6v6jl3dp4t6c9t9rk99cd8pm7utl is the only one receiving all the ==Donated Vesting tokens== in August and September with a total of 17M ==$LUNA== Received from 9 different Users.

$LUNA being IBC-ed OUT

As explained before in the terra.core.ez_transfers there are atransfer_type column which has only IBC_Transfer_In and IBC_Transfer_off Values so i assumed that the first means a IBC Bridge and the Second is not a Bridge (A terra Transaction). So i used these type of Transfers as IBC Bridges.

Top 100 Richlist of Users

Here are the top 100 Users with Most TVL on $LUNA based on the total activity of terra.core.ez_transfers i calculated the $LUNA amount of Users, and it was using this simple operation:

• $LUNA== Amount Received - ==$LUNA== Amount Sent

And then after joining the tables by User, we get the following Metrics.

$LUNA Staked, Stake Activity on Terra and Top 100 Richlist of Validators by $LUNA Staked

These metrics are the easily to get thanks to terra.core.ez_staking table, this table already have the $LUNA amount with the Decimal Convertion in the amount column, and the action column have the type of Stake, So most of the data can be calculated easily with the help of block_timestamp, to get the Percentage of $LUNA Staked i used my previous metrics of total $LUNA Supply, and finally to the top 100 Richlist of Validators i used the same method before but now with the terra.core.ez_staking, the Validator_Address and then filter by action to do again a simple operation.

Validator $LUNA Staked== = ==$LUNA Delegate + $LUNA Redelegate - $LUNA Undelegate

🔍$LUNA Staked, Stake Activity on Terra - Analysis 🔍

• Delegate ==$LUNA== (Stake) is the action most frequent in Terra, with the biggest numbers of transactions each day, Undelegate (Unstake) and Redelegate (Move Stake to Another Validator) Actions are less frequent in the long run, but at the start of the New Terra we can see a lot of activity from all type of Actions.
  • Stake activity seems to have periods of time with less activity, such as the end of August and October, or December, in November the activity of Stakes in Terra was at their highest spikes without considering the launch date.
• Most $LUNA Staked Volume comes always from Redelegate Options, Undelegate also presents a lot of $Luna Volume and Delegate doesn’t present too much $LUNA volume considering all the activity it has.
• It’s seems that the Daily Percentage of $LUNA Staked vs Total $LUNA Transferred also presents problems with values, is strange because stake activity are also registered in the EZ_Transfer table, but i decided to keep there to show how the Amount of $LUNA Staked can be higher than the ==$LUNA Transfer Flow.

Stacking Rewards distributed in $LUNA and $USD to Delegators & Validators Commission

Using terra.core.ez_transfers again we can filter by Message_Type, to get only the Rewards transfers, and then calculate the amount of $LUNA using the same method as before with the conversion to integer and Decimal correction, but this time i joined my table to calculate ==$LUNA== $USD Price using swaps to axlUSDC and axlUSDT, from the terra.core.ez_swaps, because of that only data from June 3 onwards is available.

Message_TYPE = /cosmos.distribution.v1beta1.MsgWithdrawDelegatorReward -- /cosmos.distribution.v1beta1.MsgWithdrawValidatorCommission

🔍Stacking Rewards distributed in $LUNA and $USD to Delegators & Validators Commission - Analysis 🔍

• The delegators in ==Terra== are the ones claiming most rewards and total ==$LUNA== and $USD Volume, we can see that in November 18 the amount of $LUNA being rewarded was almost 4M with an $USD equivalent of 6M $USD, in September 9 the Amount of $LUNA was low but thanks to the $LUNA $USD Price increase we can see that is the second spike in Rewards claimed for delegators.
• It’s seems that stacking rewards activity is very healthy and the Delegators are the ones with most winnings, in all the days,

Methodology

Using Flipside Crypto Tables, I tried my best to provide all the things the bounty asked, although there are some problems i tried my best to explain my methodology in each section and try to highlight all strange results i got in my work.

I really like these type of bounties of Terra, and i hope you like it!