MLDZMNcubuser1
    Updated 2023-07-26
    -- '2023-06-23' to '2023-07-03'
    with t1 as (select
    RECORDED_HOUR as hour,
    avg(close) as price_sol
    from solana.core.fact_token_prices_hourly
    where SYMBOL ilike 'sol'
    group by 1)


    select
    count(distinct tx_id) as no_contribution,
    count(distinct signers[0]) as no_user,
    sum(transfer.amount) as total_volume,
    avg(transfer.amount) as avg_volume,
    sum(transfer.amount*price_sol) as volume_usd
    from solana.core.fact_transactions s
    join (select distinct block_timestamp, tx_id
    from solana.core.fact_events where succeeded = True
    and program_id = 'BVo5TquTYMAASZhfX392BcjFUxda6DKzHStNapJE6Wyz') events
    using(tx_id, block_timestamp)
    join lateral flatten (input => log_messages) logs
    join (select tx_id, block_timestamp,amount
    from solana.core.fact_transfers where mint='So11111111111111111111111111111111111111112') transfer
    using(tx_id, block_timestamp)
    left join t1 on date_trunc('hour',s.block_timestamp)=t1.hour
    where BLOCK_TIMESTAMP>='2023-06-15' --between '2023-06-23' and '2023-07-03'
    and logs.value in ('Program log: Instruction: CreateContributionSol','Program log: Instruction: UpdateContributionSol')
    limit 5
    Run a query to Download Data