with active_wallets as ( select tx_sender, count(DISTINCT tx_id) as count_tx from terra.core.fact_transactions where tx_succeeded=TRUE group by 1 having count_tx > {{TransactionCount}}), stake as ( select action as status, trunc(block_timestamp,'{{Date}}') as month, tx_id, delegator_address as wallet from terra.core.ez_staking join active_wallets on delegator_address=tx_sender where tx_succeeded = TRUE ) , nft as ( select 'NFT' as status, trunc(block_timestamp,'{{Date}}') as month, tx_id, purchaser as wallet from terra.core.fact_nft_sales join active_wallets on purchaser=tx_sender where tx_succeeded = TRUE ) , liquidity as ( select action as status, trunc(block_timestamp,'{{Date}}') as month, tx_id, LIQUIDITY_PROVIDER_ADDRESS as wallet from terra.core.fact_lp_actions join active_wallets on liquidity_provider_address=tx_sender where currency = 'uluna' and tx_succeeded = TRUE ) , transfer as ( select 'Transfer' as status, trunc(block_timestamp,'{{Date}}') as month, tx_id, sender as wallet from terra.core.ez_transfers join active_wallets on sender=tx_sender where currency = 'uluna' and tx_succeeded = TRUE ) , swap as ( select 'Swap' as status, trunc(block_timestamp,'{{Date}}') as month, tx_id, trader as wallet from terra.core.ez_swaps join active_wallets on trader=tx_sender where tx_succeeded = TRUE ) , tb1 as ( select * from swap UNION select * from nft UNION select * from liquidity UNION select * from transfer UNION select * from stake) , tb2 as ( select month, status, wallet, count(DISTINCT tx_id) as count_tx from tb1 group by 1,2,3) select case when month >= '2022-05-01' and month < '2022-06-01' then 'May' when month >= '2022-06-01' and month < '2022-07-01' then 'June' when month >= '2022-07-01' and month < '2022-08-01' then 'July' when month >= '2022-08-01' and month < '2022-09-01' then 'August' when month >= '2022-09-01' and month < '2022-10-01' then 'September' when month >= '2022-10-01' and month < '2022-11-01' then 'October' when month >= '2022-11-01' and month < '2022-12-01' then 'November' when month >= '2022-12-01' and month < '2023-01-01' then 'December' when month >= '2023-01-01' and month < '2023-02-01' then 'January' when month >= '2023-02-01' and month < '2023-03-01' then 'February' end as splitter, status, count(DISTINCT wallet) as count_wallet, sum(count_tx) as total_transactions, avg(count_tx) as average_tx from tb2 group by 1,2

Back to Basics: Account Activity