MLDZMNcontri1
    Updated 2023-07-26
    -- forked from List of top contributors by volume of contributions @ https://flipsidecrypto.xyz/edit/queries/815b74f5-27de-48a0-9f66-c037839fcac7

    -- forked from cubuser1 @ https://flipsidecrypto.xyz/edit/queries/baf161c7-4e5c-4329-a365-e568620537c6

    -- '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),


    t2 as (select
    distinct signers[0] as contributor,
    count(distinct tx_id) as no_contribution
    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')
    group by 1
    )

    select
    case
    when no_contribution=1 then '1 time'
    when no_contribution=2 then '2 times'
    when no_contribution> 2 and no_contribution < 5 then '3-5 times'