datavortexTop Swap Pair
    Updated 2025-02-03
    WITH price AS (
    SELECT
    price,
    hour,
    token_address
    FROM
    kaia.price.ez_prices_hourly p
    ),
    pair_data AS (
    SELECT
    s.symbol_in,
    s.symbol_out,
    COUNT(DISTINCT s.tx_hash) AS TotalSwaps,
    SUM(s.amount_in * p_in.price) AS TotalSwapVolumeIn
    FROM
    kaia.defi.ez_dex_swaps s
    JOIN kaia.price.ez_prices_hourly p_in ON s.token_in = p_in.token_address
    AND date_trunc('hour', s.block_timestamp) = p_in.hour
    JOIN kaia.price.ez_prices_hourly p_out ON s.token_out = p_out.token_address
    AND date_trunc('hour', s.block_timestamp) = p_out.hour
    WHERE
    s.token_in = '0x19aac5f612f524b754ca7e7c41cbfa2e981a4432'
    GROUP BY
    s.symbol_in,
    s.symbol_out
    )
    SELECT
    CONCAT(p.symbol_in, '-', p.symbol_out) AS SwapPair,
    p.TotalSwaps,
    p.TotalSwapVolumeIn
    FROM
    pair_data p
    ORDER BY
    p.TotalSwaps DESC,
    p.TotalSwapVolumeIn DESC
    LIMIT 10;
    QueryRunArchived: QueryRun has been archived