shreexeager-harlequin
    Updated 2025-01-15
    -- STEP 1: Identify High-Performing Tokens (Bought with SOL)
    WITH token_performance AS (
    SELECT
    swap_to_mint AS token,
    COUNT(*) AS total_trades,
    SUM(swap_to_amount_usd) AS total_buy_volume_usd,
    SUM(swap_from_amount_usd) AS total_sell_volume_usd,
    AVG(CASE WHEN swap_to_amount IS NOT NULL AND swap_to_amount != 0 THEN swap_to_amount_usd / swap_to_amount ELSE 0 END) AS avg_buy_price,
    AVG(CASE WHEN swap_from_amount IS NOT NULL AND swap_from_amount != 0 THEN swap_from_amount_usd / swap_from_amount ELSE 0 END) AS avg_sell_price,
    (SUM(swap_from_amount_usd) - SUM(swap_to_amount_usd)) AS net_flow_usd
    FROM solana.defi.ez_dex_swaps
    WHERE block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '7 days'
    AND swap_from_mint = 'So11111111111111111111111111111111111111112' -- From SOL
    AND swap_to_mint IN ({{token_list}}) -- ✅ Corrected Parameter Usage
    GROUP BY swap_to_mint
    ),

    -- STEP 2: Analyze Wallets That Bought Tokens with SOL
    wallet_activity AS (
    SELECT
    swapper AS wallet,
    swap_to_mint AS token,
    COUNT(*) AS total_trades,
    SUM(swap_to_amount_usd) AS total_bought_usd,
    SUM(swap_from_amount_usd) AS total_sold_usd
    FROM solana.defi.ez_dex_swaps
    WHERE swap_to_mint IN (
    SELECT token
    FROM token_performance
    WHERE total_buy_volume_usd > 100000 AND net_flow_usd > 0
    )
    GROUP BY swapper, swap_to_mint
    ),

    -- STEP 3: Filter Wallets That Sold Tokens Back to SOL
    wallet_sells AS (
    Last run: 11 days ago
    No Data to Display
    0
    2B
    320s