MLDZMNstake4
    Updated 2023-11-25
    -- forked from stake3 @ https://flipsidecrypto.xyz/edit/queries/1fb05cdc-d7e1-43af-8323-c00a3b20a1bf

    select
    date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
    'Stake' as action,
    case
    when mint='HxhWkVpk5NS4Ltg5nij2G671CKXFRKPK8vy271Ub4uEK' then '$HXRO'
    when mint='CPwspzHc4bKtBQGNRhpRG9v3qRiPLWP28GrfZepwmBSz' then '$esHXRO'
    end as tokens,
    count(*) as no_actions,
    count(distinct tx_from) as no_users,
    sum(amount) as volume,
    avg(amount) as avg_volume,
    volume/no_users as volume_per_user

    from solana.core.fact_transfers
    where tx_to = 'BEYJqFx5G6whGmpJ4Ar9yyKfnDVBTcGWDSnJPPmsPWh6'
    and mint in ('HxhWkVpk5NS4Ltg5nij2G671CKXFRKPK8vy271Ub4uEK',
    'CPwspzHc4bKtBQGNRhpRG9v3qRiPLWP28GrfZepwmBSz')
    and BLOCK_TIMESTAMP >= current_date-{{Days_back}}
    group by 1,2,3

    union all

    select
    date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
    'Unstake' as action,
    case
    when mint='HxhWkVpk5NS4Ltg5nij2G671CKXFRKPK8vy271Ub4uEK' then '$HXRO'
    when mint='CPwspzHc4bKtBQGNRhpRG9v3qRiPLWP28GrfZepwmBSz' then '$esHXRO'
    end as tokens,
    count(*) as no_actions,
    count(distinct tx_to) as no_users,
    sum(amount) as volume,
    avg(amount) as avg_volume,
    volume/no_users as volume_per_user
    Run a query to Download Data