datavortexwhat are traders trading
    Updated 2025-02-18
    WITH swap_data AS (
    SELECT
    swap_from_mint,
    swap_to_mint,
    swap_from_symbol,
    swap_to_symbol,
    tx_id,
    swapper,
    swap_from_amount_usd,
    swap_to_amount_usd,
    block_timestamp,
    CASE
    WHEN SUM(swap_from_amount_usd) <= 1000 THEN 'Small Traders'
    WHEN SUM(swap_from_amount_usd) <= 10000 THEN 'Medium Traders'
    WHEN SUM(swap_from_amount_usd) > 10000 THEN 'Whales'
    ELSE 'Unknown'
    END AS trader_category
    FROM
    solana.marinade.ez_swaps
    WHERE
    swap_from_symbol = 'MSOL'
    AND block_timestamp >= CURRENT_DATE - INTERVAL '1 MONTH'
    GROUP BY
    swap_from_mint, swap_to_mint, swap_from_symbol, swap_to_symbol, tx_id, swapper, swap_from_amount_usd, swap_to_amount_usd, block_timestamp
    ),
    ranked_swap_pairs AS (
    SELECT
    trader_category,
    swap_from_symbol || '-' || swap_to_symbol AS swap_pair,
    COUNT(DISTINCT tx_id) AS swap_count,
    SUM(swap_from_amount_usd) AS total_swap_volume_usd,
    ROW_NUMBER() OVER (PARTITION BY trader_category ORDER BY SUM(swap_from_amount_usd) DESC) AS rank
    FROM
    swap_data
    GROUP BY
    trader_category, swap_from_symbol, swap_to_symbol