with min_date AS ( SELECT address, min(date) as address_creation_date FROM terra.daily_balances WHERE date < CURRENT_DATE --date < '2022-01-24' and address_label is NULL and address_label_type is NULL and address_label_subtype is NULL group by 1 ), staked_wallets as ( SELECT address, sum(balance) as staked_balance FROM terra.daily_balances WHERE balance >0 and balance_type in ('staked') and currency = 'LUNA' and date = CURRENT_DATE - 1 --and date < '2022-01-23' GROUP by 1 ), liquid_balance as ( SELECT address, sum(balance) as liquid_balance FROM terra.daily_balances WHERE balance > 0 and balance_type in ('liquid') and currency = 'LUNA' and date = CURRENT_DATE - 1 --and date < '2022-01-23' GROUP BY 1 ), min_date_classification as ( SELECT address, case when address_creation_date < CURRENT_DATE - 7 then 'Existing User' else 'New User' end as wallet_classification --case when address_creation_date < '2022-01-18' then 'Existing User' else 'New User' end as wallet_classification FROM min_date group by 1,2 ), deposit_txs_ls as ( SELECT msg_value:sender::string as user_ls, sum(msg_value:coins[0]:amount::float)/POW(10,6) as ls_staked FROM terra.msgs WHERE msg_value:contract::string in ('terra1xacqx447msqp46qmv8k2sq6v5jh9fdj37az898') and msg_value:execute_msg:deposit is not null and tx_status = 'SUCCEEDED' and date(block_timestamp) >= CURRENT_DATE - 7 and date(block_timestamp) <= CURRENT_DATE - 1 --and date(block_timestamp) >= '2022-01-18' --and date(block_timestamp) <= '2022-01-23' GROUP BY 1 ), deposit_txs_ps as ( SELECT msg_value:sender::string as user_ps, sum(msg_value:coins[0]:amount::float/POW(10,6)) as plain_staked_luna from terra.msgs m where msg_value:contract in ('terra1r2vv8cyt0scyxymktyfuudqs3lgtypk72w6m3m') and msg_value:execute_msg:deposit:pool_id is not NULL and tx_status = 'SUCCEEDED' and date(block_timestamp) >= CURRENT_DATE - 7 and date(block_timestamp) <= CURRENT_DATE - 1 --and date(block_timestamp) >= '2022-01-18' --and date(block_timestamp) <= '2022-01-23' group by 1 ), ts_staking_txs as ( select delegator_address as user_ts, sum(event_amount) as ts_staked_luna from terra.staking st where date(block_timestamp) >= CURRENT_DATE - 7 and date(block_timestamp) <= CURRENT_DATE - 1 --date(block_timestamp) >= '2022-01-18' --and date(block_timestamp) <= '2022-01-23' and action in ('delegate') group by 1 ), consolidated_data as ( SELECT m.address, wallet_classification, sw.address as staked_address, user_ls, user_ps, user_ts, sum(staked_balance) as staked_balance, sum(liquid_balance) as liquid_balance, sum(ls_staked) as ls_staked, sum(plain_staked_luna) as plain_staked_luna, sum(ts_staked_luna) as ts_staked_luna from min_date_classification m left JOIN staked_wallets sw on m.address = sw.address left JOIN liquid_balance lb on m.address = lb.address left join deposit_txs_ls dls on m.address = dls.user_ls left join deposit_txs_ps dps on m.address = dps.user_ps left join ts_staking_txs dts on m.address = dts.user_ts group by 1,2,3,4,5,6 ) SELECT wallet_classification, count(distinct address) as total_addresses, count(distinct staked_address) as staked_addresses, count(distinct user_ls) as liquid_staked_last_week, count(distinct user_ps) as plain_staked_last_week, count(distinct user_ts) as terra_staked_last_week, sum(liquid_balance) as liquid_balance, sum(staked_balance) as staked_balance, sum(ls_staked) as ls_staked, sum(plain_staked_luna) as plain_staked_luna, sum(ts_staked_luna) as ts_staked_luna FROM consolidated_data group by 1

Stader - Liquid & Plain Staking