block_timestamp,

tx_id,

MSG_VALUE:coins[0]:amount::string/1e6 as amount,

MSG_VALUE:coins[0]:denom::string as denom,

MSG_VALUE:sender::string as sender,

MSG_VALUE:contract::string as contract,

MSG_VALUE:execute_msg:deposit:pool_id::string as pool_id, case

when pool_id = 0 then 'Blue_Chip'

when pool_id = 1 then 'Community'

when pool_id = 2 then 'Airdrop_Plus' else null

end as staking_pool

--0.blue_chip, 1.community, 2.airdrop_plus

from terra.msgs where contract = 'terra1r2vv8cyt0scyxymktyfuudqs3lgtypk72w6m3m' and tx_status = 'SUCCEEDED')

select date_trunc('day', block_timestamp) as dt, staking_pool, sum(amount) as LUNA_deposited, sum(luna_deposited) over (partition by staking_pool order by dt) as cumulative_staked,

count(distinct sender) as sender_count, count(distinct tx_id) as tx_count

from raw

where pool_id is not null

group by 1,2