block_timestamp::date as day,

iff(event_type = 'withdraw', 'Unstake', 'Stake') as type,

count(distinct(tx_id)) as txn_count,

count(distinct(signers[0])) as users_count,

sum(abs((pre_balances[0] - post_balances[0]) / pow(10, 9))) as total_amount,

avg(abs((pre_balances[0] - post_balances[0]) / pow(10, 9))) as avg_amount,

median(abs((pre_balances[0] - post_balances[0]) / pow(10, 9))) as median_amount

from solana.core.fact_staking_lp_actions

where succeeded = 1

and (

event_type = 'withdraw' or

event_type = 'delegate'

and block_timestamp::date >= '2022-11-01'

and program_id = 'Stake11111111111111111111111111111111111111'

group by day, type

order by day asc