Assignment 6 Question 5
For Solana holders, they can stake SOL into SOL networks or DeFi platforms. Let's analyze SOL staking into DeFi platforms. Analyze SOL addresses involving SOL staking to DeFi platforms for the past month.
a. Find SOL stakers' addresses and DeFi platforms they use for the past month. First, we need to identify stakers' addresses using the staking_lp_actions table. Then show a table with time stamps, stakers' addresses, and DeFi platforms (Be aware not to include direct staking activities into SOL networks. Please refer to the sample dashboard).
b. Find the number of stakers and the average number of platforms that a staker has used. Create a visualization of the daily number of stakers' addresses regardless of platforms.
c. Analyze DeFi platforms. Create a pie chart showing the proportions of how much each platform is used. Which platform is the most popular?
After analyzing, we found out that “pyth” is the most popular DeFi platform.
For the additional question, we would like to find out what date has the highest transaction number last month. As it turns out, 2022-06-13 has the highest transaction number among the other days.
Also, what is the average number of platforms a staker has used for the past month?
The average number of platforms a staker has used last month is around 1.33.
In this dashboard, we are going to analyze the SOL stakers’ activities through different platforms. Anticipate to get some insights by looking at the Flipside Crypto database.
Appendix: --5a
with staker_address as ( select signers[0] as address -- staker's address from solana.core.fact_staking_lp_actions where event_type = 'delegate' -- staking activity group by address ) select left(block_timestamp,10) as date, t.signers[0] as staker, l.label as DeFi_platform from solana.core.fact_transactions t join solana.core.dim_labels l on t.instructions[0]:programId = l.address where t.signers[0] in (select * from staker_address) and l.label != 'solana' -- this means staking directly on SOL networks, not DEXs and succeeded = true and date >= '2022-06-01' and date <= '2022-06-30' order by date --5b
with staker_address as ( select signers[0] as address -- staker's address from solana.core.fact_staking_lp_actions where event_type = 'delegate' -- staking activity group by address ) select left(block_timestamp,10) as date, count(DISTINCT t.signers[0]) as stakers_addresses from solana.core.fact_transactions t join solana.core.dim_labels l on t.instructions[0]:programId = l.address where t.signers[0] in (select * from staker_address) and l.label != 'solana' -- this means staking directly on SOL networks, not DEXs and succeeded = true and date >= '2022-06-01' and date <= '2022-06-30' group by date order by date
select AVG(m.DeFi_num) as avg_u_want FROM (with staker_address as ( select signers[0] as address -- staker's address from solana.core.fact_staking_lp_actions where event_type = 'delegate' -- staking activity group by address ) select t.signers[0] as staker, count(DISTINCT l.label) as DeFi_num from solana.core.fact_transactions t join solana.core.dim_labels l on t.instructions[0]:programId = l.address where t.signers[0] in (select * from staker_address) and l.label != 'solana' -- this means staking directly on SOL networks, not DEXs and succeeded = true and left(block_timestamp,10) >= '2022-06-01' and left(block_timestamp,10) <= '2022-06-30' group by staker order by staker ) as m --5c
with staker_address as ( select signers[0] as address -- staker's address from solana.core.fact_staking_lp_actions where event_type = 'delegate' -- staking activity group by address ) select l.label as platforms, count(*) as platform_count from solana.core.fact_transactions t join solana.core.dim_labels l on t.instructions[0]:programId = l.address group by Platforms order by Platforms --5d
with staker_address as ( select signers[0] as address -- staker's address from solana.core.fact_staking_lp_actions where event_type = 'delegate' -- staking activity group by address ) select left(block_timestamp,10) as date, count(*) as number_of_transactions from solana.core.fact_transactions t join solana.core.dim_labels l on t.instructions[0]:programId = l.address where t.signers[0] in (select * from staker_address) and l.label != 'solana' -- this means staking directly on SOL networks, not DEXs and succeeded = true and date >= '2022-06-01' and date <= '2022-06-30' group by date order by date
From this experience, we found the stakers in the transaction and how many DeFi platforms are used, as well as which one is the most popular. We also did a further research on which date has the most transaction.