Flipside Community6 - solana most popular dex power users copy copy
    Updated 2024-11-04
    -- forked from 6 - solana most popular nft power users copy @ https://flipsidecrypto.xyz/edit/queries/160b210b-1225-4f9c-9595-97556d4c2cab

    -- forked from 6 - solana most popular dex power users @ https://flipsidecrypto.xyz/edit/queries/18b39f2c-975d-4c21-a9c7-8b79be5a5dcb

    with number_of_transactions as (
    select
    signer as user,
    num_txs as cnt
    from solana.core.ez_signers
    where num_txs >= 100
    )

    select
    case
    when swap_program ilike '%jupiter%' then 'jupiter'
    when swap_program ilike '%orca%' then 'orca'
    when swap_program ilike '%raydium%' then 'raydium'
    when swap_program ilike '%saber%' then 'saber'
    else swap_program
    end as swap_program,
    count(distinct swapper) as users
    -- ,count(distinct tx_hash) as transactions
    from solana.defi.fact_swaps
    where swapper in (select user from number_of_transactions)
    and block_timestamp::date >='2023-01-01'
    and block_timestamp::date < '2024-01-01'
    group by 1
    order by 2 desc
    limit 5
    ;

    -- /*
    -- [1] "1 - solana acquired_monthly.csv"
    -- [2] "2 - solana acquired_yearly -not-yearlylol.csv"
    -- [3] "3 - solana return stats.csv"
    -- [4] "4 - solana acquired by tx count bucket.csv"