marquMarinade Growth - Staking actions breakdown bars granular
    Updated 2022-11-23
    with

    delayed_unstake as (

    select

    block_timestamp,
    tx_id,
    f.value :parsed :info :amount ::int as amount

    from solana.core.fact_events events
    inner join lateral flatten (input => inner_instruction :instructions) f
    inner join solana.core.fact_stake_pool_actions spool
    using(tx_id,block_timestamp,succeeded)
    where succeeded
    and block_timestamp ::date > current_date() - interval '{{period_months}} months'
    and spool.action = 'order_unstake'
    and spool.stake_pool_name = 'marinade'
    and f.value :parsed :type ::string = 'burn'
    and f.value :parsed :info :authority ::string = spool.address
    ),
    pool_actions as (

    select

    block_timestamp,
    stake_pool_name,
    case
    when action in ('deposit','deposit_dao','deposit_stake','deposit_dao_stake','deposit_dao_with_referrer') then 'DEPOSIT'
    else 'WITHDRAW'
    end as action_label,
    case when action_label = 'DEPOSIT' then coalesce(spool.amount,dstake.amount) / pow(10,9)
    else coalesce(spool.amount,dstake.amount) / pow(10,9) * (-1)
    end as amount,
    address as user_address,
    Run a query to Download Data