block_timestamp::date as day,

stake_pool_name as pool,

stake_pool as pool_address,

count(distinct(tx_id)) as txn_count,

count(distinct(address)) as stakers_count,

(txn_count / stakers_count) as avg_txn_per_staker,

sum(amount/ pow(10, 9)) as total_amount,

avg((amount/ pow(10, 9))) as avg_amount,

median((amount/ pow(10, 9))) as median_amount

from solana.core.fact_stake_pool_actions

where succeeded = 1

and action ilike '%deposit%'

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

group by day, stake_pool_name, stake_pool

order by day asc