Mrfti$BONK Net Swap Volume by DEX copy
    Updated 2023-04-23
    -- forked from kellen / $BONK Net Swap Volume by DEX @ https://flipsidecrypto.xyz/kellen/q/2023-01-03-12-37-am-UsUzVT

    WITH t0 AS (
    SELECT INITCAP(SPLIT(swap_program, ' ')[0]::string) AS dex
    , COUNT(DISTINCT CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_sellers
    , COUNT(DISTINCT CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_buyers
    , COUNT(DISTINCT swapper) AS unique_swappers
    , SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN -swap_from_amount ELSE swap_to_amount END) AS net_swap_amout
    , SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_from_amount ELSE 0 END) AS swap_from_amount
    , SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE 0 END) AS swap_to_amount
    , SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE swap_from_amount END) AS swap_volume
    FROM solana.core.fact_swaps
    WHERE block_timestamp >= '2022-12-25'
    AND (
    swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
    OR swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
    )
    AND swap_from_mint <> swap_to_mint
    AND succeeded
    GROUP BY 1
    )
    SELECT *
    , CASE WHEN net_swap_amout > 0 THEN 'Net Buy Volume' ELSE 'Net Sell Volume' END AS category
    FROM t0


    Run a query to Download Data