Try Quests to earn cryptoNew Year, New LUNA?
The holidays and New Year are often chaotic in the crypto and DEFI space, as users make a spree of new transactions and wallets as they receive (and give) some cash and coins as holiday gifts.
New Year, New LUNA?
The holidays and New Year are often chaotic in the crypto and DEFI space, as users make a spree of new transactions and wallets as they receive (and give) some cash and coins as holiday gifts. In this dashboard, we want to check that:
> Has this flurry of winter activity impacted the Terra ecosystem? Are users creating new wallets or buying tokens with their newfound holiday wealth? Are they staking all those new tokens once they get them? Or are they selling tokens to pay for their own gifts and holiday travel?
📝 Description of Work
In this dashboard, we want to examine the activity of terra users during the Christmas holidays. For this, we want to examine the following:
- New wallets during and before the Christmas holidays
- Transactions and fees associated with them during and before the Christmas holidays :
- Number of transactions and fees associated with them by daily, weekly, total and average before the holiday and during the holiday
- Transaction activity by new wallets and old wallets
- LUNA transfer during and before the Christmas holidays:
- Number of LUNA transfer by daily, weekly, total and average before the holiday and during the holiday
- Volume of LUNA transfer by daily, weekly, total and average before the holiday and during the holiday
- LUNA transfer activity by new wallets and old wallets
- LUNA swaps (==From/To==) during and before the Christmas holidays:
- Number of LUNA swaps by daily, weekly, total and average before the holiday and during the holiday
- Volume of LUNA swaps by daily, weekly, total and average before the holiday and during the holiday
- LUNA swaps activity by new wallets and old wallets
- Staking (==Stake/UnStake==) during and before the Christmas holidays:
- Staking amount by daily, weekly, total and average before the holiday and during the holiday
- Staking activity by new wallets and old wallets
- NFTs Mint during and before the Christmas holidays
- Number of NFTs mint by daily, weekly, total and average before the holiday and during the holiday
- Volume of NFTs mint by daily, weekly, total and average before the holiday and during the holiday
- Number of NFTs minters by daily, weekly, total and average before the holiday and during the holiday
- NFTs Mint activity by new wallets and old wallets
- NFTs Sales during and before the Christmas holidays
- Number of NFTs sales by daily, weekly, total and average before the holiday and during the holiday
- Volume of NFTs sales by daily, weekly, total and average before the holiday and during the holiday
- Number of NFTs sellers by daily, weekly, total and average before the holiday and during the holiday
- NFTs sales activity by new wallets and old wallets
- NFTs purchase during and before the Christmas holidays
- Number of NFTs purchase by daily, weekly, total and average before the holiday and during the holiday
- Volume of NFTs purchase by daily, weekly, total and average before the holiday and during the holiday
- Number of NFTs buyers by daily, weekly, total and average before the holiday and during the holiday
- NFTs purchase activity by new wallets and old wallets
- Contracts deployed during and before the Christmas holidays
- Number of contracts deployed by daily, weekly, total and average before the holiday and during the holiday
- LUNA Supply
🧠 Definitions and Methods
To deal with this dashboard, we use terra.core schema and fact_transactions, ez_staking, ez_swaps, ez_transfers, fact_nft_mints, fact_nft_sales and ez_messages tables for to analyze and we used the fact_hourly_prices table in the crosschain.core schema for the daily price of LUNA.
Selected time period:
- To examine user activity in the terra ecosystem, we divided the time period into two parts: two weeks before the Christmas holidays and two weeks during the Christmas holidays.
- The period is from ==2022-12-12== to ==2023-01-06==.
- Two weeks before the Christmas holidays: 2022-12-12 to 2022-12-24
- Two weeks during the Christmas holidays: 2022-12-25 to 2023-01-06
- The period is from ==2022-12-12== to ==2023-01-06==.
New wallets:
- To get the new wallets, we use the fact_transactions table, get the first transaction from each wallet, and then consider the wallets whose first transaction in the selected time period is the new wallet.
new_users as (
select tx_sender as wallet_address, min(block_timestamp)::date as creation_date from terra.core.fact_transactions group by wallet_address
)
LUNA transfer:
- To get the LUNA transfers, we use the ez_transfers table and then get the total LUNA send and receive for each wallet separately and then calculate the transfer changes on a daily basis.
select days, address, sum(amount) as amount_of_transfers,
sum(amount_of_transfers) over (partition by address order by days asc rows between unbounded preceding and current row) as daily_transfers
from transfer_luna_from_user group by days, address
Staking:
- To check the Staking, we use the ez_staking table and get the amount of Delegate (Stake) and Undelegate (UnStake) for each wallet separately and calculate their daily changes to get the total volume of Stake and Unstake.
delegate_luna as (
select block_timestamp::date as days, delegator_address as address, sum(amount) as amount from terra.core.ez_staking
where action = 'Delegate' and tx_succeeded = true and block_timestamp::date >= '2022-12-12' and block_timestamp::date < '2023-01-7'group by days, address
),
undelegate_luna as (
select block_timestamp::date as days, delegator_address as address,- sum(amount) as amount from terra.core.ez_staking
where action = 'Undelegate'and tx_succeeded = true and block_timestamp::date >= '2022-12-12' and block_timestamp::date < '2023-01-7' group by days, address
),
LUNA Supply:
- To get the LUNA Supply, since we don't have the daily balance table for the wallets, we use the ez_transfers table to calculate the current amount of supply. For this purpose, for each wallet, we calculate the total sum of sending LUNA and the total sum of receiving LUNA, then subtract these two values for each wallet and finally get the total balance of the wallets as Supply.
Contracts Deployed:
- To get Contracts Deployed, we use the ez_messages table and use the message_type field
(message_type = '/cosmwasm.wasm.v1.MsgExecuteContract'), then for each contract we consider the first transaction related to the ==ExecuteContract== as deploy contracts.
✅ Observations
In the charts above, you can see the total number of new wallets and the charts of the creation of new wallets daily and weekly in the selected time period (2022-12-12 to 2023-01-06).
- As you can see, the total number of wallets created during the Christmas holiday is 1728 wallets, which is 1790 before the Christmas holiday.
- The daily average of wallets created during the Christmas holidays is 133, and the weekly average is 576.
- At the beginning of the Christmas holidays, that is, on December 25, it has grown significantly until December 27, and then it has had a slight downward trend, and it has taken an upward trend until ==December 31==. As of January 1, this trend is completely downward.
- The activity of creating new wallets is higher in the first week of the holiday than in the second week (This amount is reduced by almost half).
✅ Observations
In the charts above, you can see the number of transactions and their fees during the Christmas holidays and before that on a daily and weekly basis, and the total number of transactions based on the type of user (new users and old users).
- As you can see, the total number of transactions during the Christmas holiday and before is almost equal.
- The average daily transactions during and before the Christmas holiday are almost the same
- Interestingly, the average weekly transactions during the Christmas holiday are lower for old users than before the holiday, but the same for new users.
- The number of transactions for old users is almost 25 times that of new users, which means that users prefer to make more transactions than to create a new wallet and make transactions with it.
- From December 25 and the beginning of the Christmas holidays, the number of transactions shows an upward trend, and this upward trend continues until January 1, and then the downward trend begins.
- From December 25 and the beginning of the Christmas holidays, the number of transactions for new wallets shows an upward trend
- Transaction fees during the Christmas holidays are slightly higher than before the Christmas holidays
- The transaction fee ratio for new users is higher than their number of transactions, meaning that new users paid more fee than old users during the Christmas holidays (Ratio).
- The fee of transactions has an upward trend from the beginning of the Christmas holiday (December 25) to January 1, and then it goes down a bit and then goes up again.
- The fee of transactions for new users shows a completely upward trend from December 25 onwards
- The total number of transactions and its weekly average are higher in the first week of the holiday than in the second week
- The fee of transactions in the first week of the holiday is higher than in the second week, but it is interesting that their weekly average is almost the same
✅ Observations
In the charts above, you can see the sending and receiving of LUNA by new users and old users during the Christmas holidays and before.
- As you can see, sending and receiving LUNA for old users is almost the same, and the volume of sending and receiving for LUNA does not show a noticeable change.
- For new users, the volume of receiving LUNA from the beginning of the Christmas holiday shows an upward trend compared to sending LUNA, while before the Christmas holiday, this ratio for sending and receiving LUNA is almost equal.
- For old users, the volume of receiving LUNA from the beginning of the Christmas holiday onwards does not show an upward trend compared to sending LUNA, while before the Christmas holiday, this ratio for sending and receiving LUNA is almost equal.
- The above charts show that users receive more LUNA after creating a wallet during the Christmas holidays, while there is no change in sending and receiving LUNA for old users.
✅ Observations
In the charts above, you can see the amount of Stake and UnStake for LUNA by new users and old users during the Christmas holidays and before.
- As you can see, the volume of LUNA staking shows a significant upward trend during the Christmas holidays for both old and new users.
- The volume of Stake and UnStake for old users is almost equal before the Christmas holiday, but after the Christmas holiday, the volume of Stake compared to UnStake is almost doubled.
- For new users, the volume of Stake compared to UnStake before the Christmas holiday is almost 3 times, but after the Christmas holiday, the volume of Stake compared to UnStake has increased greatly.
- The above results show that users are more likely to Stake LUNA after the Christmas holiday, while LUNA transfer has a steady trend.
✅ Observations
In the charts above, you can see the volume, number of swaps and number of Swappers to/from LUNA to/from other tokens by old users and new users during the Christmas holidays and before.
- As you can see, one day before the start of the holiday, i.e. December 24, compared to the start of the holiday, the volume of swaps from/to LUNA from/to other tokens has increased.
- The volume of swaps during the Christmas holiday and the first week of the holiday does not show any particular trend, but on December 30, the volume of swaps by old users from LUNA to other tokens has increased dramatically.
- The volume of swaps to LUNA from other tokens increased significantly at the end of the Christmas holidays by old users, on January 4th and 6th.
- This trend is the opposite for new users, that is, at the end of the holiday, the volume of swaps from LUNA to other tokens (January 5) has increased dramatically.
- In general, the volume of swaps before and during the holidays does not show a noticeable change
- For the number of swaps from/to LUNA during the Christmas holidays and the first week of Christmas by old users, it shows an upward trend, but in the second week, this trend is downward for old users.
- It shows an upward trend for the number of swaps from/to LUNA during the Christmas holidays for new users
- The number of Swappers before and during the Christmas holidays for old users shows the same trend and does not show a noticeable change.
- In general, the volume, the number of swaps and the number of swappers for swaps from LUNA to other tokens are more than for swaps to LUNA from other tokens.
✅ Observations
The above charts show the number of Mints, number of minters and Mint NFT volume for new and old users during and before the Christmas holidays.
- As you can see, the volume, number, and number of minters for NFT increased during the Christmas holiday compared to before the holiday for all users.
- At the beginning of the Christmas holidays, Mint NFT activity has increased significantly for new users compared to before, but this increase is not very significant for new users.
✅ Observations
In the charts above, you can see NFT sales by new wallets and old users during the Christmas holidays and before
- As you can see, NFT sales increased during the Christmas holiday compared to before by both new and old users
- The sale of NFT shows an upward trend at the beginning of the holidays, but it has a lot of fluctuations, but the total number of sales shows a significant growth.
- In the week of the holiday, NFT sales show a decreasing trend
- 21 of the new users have sold NFT while only 113 of the old users have sold NFT. This means that the ratio of NFT sales by new users to the ratio of total users is higher than old users
- Before the start of the holidays, the sales volume of NFTs shows a significant downward trend
✅ Observations
The charts above show the purchase of NFT by new and old users during and before the Christmas holidays
- Only two of the new wallets purchased NFTs during the Christmas holidays
- 109 of the old wallets bought NFTs during the Christmas holidays, and this shows that old users were more active than new users in buying NFTs, as opposed to selling NFTs.
- NFT buying increased during the Christmas holidays compared to before
- In the second week of the holiday buying NFT has decreased
- NFT purchases by new wallets during and before the Christmas holidays remained unchanged
✅ Observations
In the charts above, you can see Supply for LUNA and the daily balance of LUNA users
- Supply is currently for LUNA 187M
- The daily balance changes of users before and after the Christmas holidays do not show a noticeable change, but on January 1, it shows a significant growth.
- The daily balance of new users shows an upward trend at the beginning of the Christmas holidays
✔️ Final Conclusion
In this dashboard, we will examine the terra ecosystem during the Christmas holidays (2022-12-25 to 2023-01-06) and before the Christmas holidays (2022-12-12 to 2022-12-24) in terms of the number of wallets New, number of transactions and transaction fee, receive and send LUNA, Stake LUNA, Swap from/to LUNA, NFTs Mint, NFTs Sales, NFT Purchase, Supply, Daily Balance and Contracts deployed.
-
According to the obtained results, it was observed that the activity of users during the Christmas holidays increased in terms of the number of transactions for both new and old users, and more transactions were made by users in the first week and the beginning of the holiday. In the second week of the holiday, transactions show a downward trend.
-
The number of wallets created at the beginning of the Christmas holiday and in the first week increased, but in the second week of the holiday, it showed a decreasing trend.
-
The fee of transactions during the Christmas holiday shows a greater increase in proportion to new users and the number of transactions made by new users than old users.
-
It was observed that the sending and receiving of LUNA during the Christmas holiday and before it did not change significantly for old users, but new users received more LUNA.
-
It was observed that the volume of stakes increased at the beginning of the holiday and after that, and more users preferred to stake their LUNA.
-
New users who entered the terra ecosystem during the Christmas holidays and received more LUNA to stake it
-
The number of swaps and swappers increased during the Christmas holidays for LUNA and in the first week, but the volume of swaps did not change significantly.
-
The balance of users during the Christmas holidays and before it does not show a noticeable change
\
> According to the obtained results, it can be concluded that users prefer to stake more of their LUNA, and this action is seen more by new users, after Stake, users are more likely to swap and transfer LUNA and the activity related NFTs and Contracts deployed during the holidays are next. It is also interesting that the daily balance of LUNA for users has not decreased or increased significantly, and the daily balance of new users shows an upward trend at the beginning of the holidays.