datavortexweekly swap pair
    Updated 2025-01-16
    WITH weekly_swaps AS (
    SELECT
    DATE_TRUNC('week', block_timestamp) AS week,
    CONCAT(symbol_in, ' -> ', symbol_out) AS swap_pair,
    CONCAT(token_in_contract, ' -> ', token_out_contract) AS token_pair,
    COUNT(DISTINCT tx_hash) AS swaps,
    SUM(amount_in_usd) AS volume
    FROM
    near.defi.ez_dex_swaps
    WHERE
    platform = 'v2.ref-finance.near'
    AND amount_in_usd IS NOT NULL
    GROUP BY
    week, symbol_in, symbol_out, token_in_contract, token_out_contract
    ),
    top_weekly_swaps AS (
    SELECT
    week,
    swap_pair,
    token_pair,
    swaps,
    volume
    FROM
    weekly_swaps
    QUALIFY ROW_NUMBER() OVER (
    PARTITION BY week
    ORDER BY volume DESC, swaps DESC
    ) <= 5
    )
    SELECT
    week,
    swap_pair,
    token_pair,
    swaps,
    volume
    FROM
    QueryRunArchived: QueryRun has been archived