MLDZMNSSM1
    Updated 2022-11-14
    select
    case
    when action ilike '%deposit%' then 'Stake'
    when action ilike '%withdraw%' then 'Unstake'
    when action ilike '%claim%' then 'Claim'
    else null end as action_type,
    count(distinct tx_id) as no_txn,
    count(distinct ADDRESS) as no_users,
    count(distinct STAKE_POOL) as no_pools,
    sum(amount/pow(10,9)) as volume,
    avg(amount/pow(10,9)) as avg_volume,
    median(amount/pow(10,9)) as median_volume,
    min(amount/pow(10,9)) as min_volume,
    max(amount/pow(10,9)) as max_volume,

    count(distinct date_trunc(day, block_timestamp)) as average_buyer_day,
    volume/count(distinct date_trunc(day, block_timestamp)) as average_volume_day
    from solana.core.fact_stake_pool_actions
    where SUCCEEDED='TRUE'
    and BLOCK_TIMESTAMP>='2022-11-01' --past week
    group by 1 having action_type is not null
    Run a query to Download Data