MLDZMNhpf8
    Updated 2023-03-23
    with t1 as (select
    RECORDED_HOUR::date as day,
    avg(close) as price
    from solana.core.fact_token_prices_hourly
    where symbol ='SOL'
    group by 1),

    t2 as (select
    distinct INNER_INSTRUCTION:instructions[0]:parsed:info:destination as collected_wallets,
    sum(INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9) as volume_sol,
    sum((INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9)*price) as volume_usd

    from solana.core.fact_events s left join t1 a on s.BLOCK_TIMESTAMP::date=a.day
    where PROGRAM_ID='3VtjHnDuDD1QreJiYNziDsdkeALMT6b2F9j3AXdL4q8v'
    and SUCCEEDED='TRUE'
    group by 1),

    t3 as ( select
    distinct TX_TO as collected_wallets1,
    sum(AMOUNT) as sent_sol
    from solana.core.fact_transfers
    where BLOCK_TIMESTAMP>='2022-03-04' and BLOCK_TIMESTAMP<='2022-04-24'
    and TX_FROM in (select collected_wallets from t2)
    group by 1
    )

    select
    BLOCK_TIMESTAMP::date as date,
    l.LABEL_TYPE as type,
    count(distinct TX_FROM) as no_wallets,
    sum(AMOUNT) as sent_sol
    from solana.core.fact_transfers t join solana.core.dim_labels l on t.TX_TO= l.address
    where BLOCK_TIMESTAMP>='2022-03-04' and BLOCK_TIMESTAMP<='2022-04-24'
    --and l.label != 'solana'
    and TX_FROM in (select collected_wallets1 from t3)
    group by 1,2
    Run a query to Download Data