Updated 2023-08-29
    with price_tab as (SELECT
    recorded_hour::date as day,
    avg(close) as token_price
    FROM solana.core.ez_token_prices_hourly
    WHERE day >=current_date-{{Days_back}}
    AND token_address = 'So11111111111111111111111111111111111111112'
    group by 1
    )

    select
    s.BLOCK_TIMESTAMP::date as date,
    'by Sanctum' as type,
    count(distinct tx_id) as no_txn,
    count(distinct signers[0]) as no_users,
    no_txn/no_users as txn_per_user,
    sum(amount) as volume_sol,
    sum(amount*token_price) as volume_usd,
    avg(amount*token_price) as avg_volume_usd
    from solana.core.fact_transactions s
    join (select tx_id
    from solana.core.fact_events where succeeded = True
    and program_id = 'unpXTU2Ndrc7WWNyEhQWe4udTzSibLPi25SXv2xbCHQ') events
    using(tx_id)
    join (select *
    from solana.core.fact_transfers
    where tx_from='3rBnnH9TTgd3xwu48rnzGsaQkSr1hR64nY71DrDt6VrQ'
    ) transfer
    using(tx_id)
    join lateral flatten (input => log_messages) l
    left join price_tab p on s.BLOCK_TIMESTAMP::date=p.day
    where (l.value ilike 'Program log: Instruction: Unstake')
    and succeeded
    and BLOCK_TIMESTAMP>=current_date-{{Days_back}}
    group by 1,2

    union all
    Run a query to Download Data