with all_users as ( SELECT case when block_timestamp >= CURRENT_DATE - 8 and block_timestamp <= CURRENT_DATE - 1 then 'Week 3' when block_timestamp >= CURRENT_DATE - 15 and block_timestamp <= CURRENT_DATE - 8 then 'Week 2' when block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 15 then 'Week 1' end as Timeline, count(distinct TX_HASH) as "Total Number of Transactions", count(distinct TX_SIGNER) as "Total Number of User" FROM near.core.fact_transactions WHERE block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 1 and TX_STATUS = 'Success' GROUP by 1 order by 1 ), transfer as (SELECT case when block_timestamp >= CURRENT_DATE - 8 and block_timestamp <= CURRENT_DATE - 1 then 'Week 3' when block_timestamp >= CURRENT_DATE - 15 and block_timestamp <= CURRENT_DATE - 8 then 'Week 2' when block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 15 then 'Week 1' end as "Timeline", count(distinct TX_HASH) as "Total Number of Transfers", count(distinct TX_SIGNER) as "Total Number of Transferrer" FROM near.core.fact_transfers WHERE block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 1 and STATUS = 'TRUE' GROUP by 1 order by 1), swap as (SELECT case when block_timestamp >= CURRENT_DATE - 8 and block_timestamp <= CURRENT_DATE - 1 then 'Week 3' when block_timestamp >= CURRENT_DATE - 15 and block_timestamp <= CURRENT_DATE - 8 then 'Week 2' when block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 15 then 'Week 1' end as "Timeline", count(distinct TX_HASH) as "Total Number of Swaps", count(distinct TRADER) as "Total Number of Swappers" FROM near.core.ez_dex_swaps WHERE block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 1 GROUP by 1 order by 1), stake as ( SELECT case when block_timestamp >= CURRENT_DATE - 8 and block_timestamp <= CURRENT_DATE - 1 then 'Week 3' when block_timestamp >= CURRENT_DATE - 15 and block_timestamp <= CURRENT_DATE - 8 then 'Week 2' when block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 15 then 'Week 1' end as "Timeline", count(distinct TX_HASH) as "Total Number of Actions", count(distinct TX_SIGNER) as "Total Number of Stakers" FROM near.core.dim_staking_actions WHERE block_timestamp >= CURRENT_DATE - 22 and block_timestamp <= CURRENT_DATE - 1 GROUP by 1 order by 1 ) select b."Timeline", ("Total Number of Transfers" / "Total Number of Transactions" * 100) as "Transfers / All Transactions Ratio", ("Total Number of Transferrer" / "Total Number of User" * 100) as "Transferrer / All Users Ratio", (("Total Number of Swaps" / "Total Number of Transactions") * 100) as "Swaps / All Transactions Ratio", ("Total Number of Swappers" / "Total Number of User" * 100) as "Swappers / All Users Ratio", ("Total Number of Actions" / "Total Number of Transactions" * 100) as "Stakes + Unstakes / All Transactions Ratio", ("Total Number of Stakers" / "Total Number of User" * 100) as "Stakers / All Users Ratio" from all_users a join transfer b on b."Timeline" = a.Timeline join swap c on c."Timeline" = a.Timeline join stake d on d."Timeline" = a.Timeline order by 1

[NEAR] - Insight of the Week