flipside

    victorious-purple
    gigiokobavictorious-purple
    Updated 2024-11-03
    WITH swaps AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS swap_date,
    CASE
    WHEN SYMBOL_IN < SYMBOL_OUT THEN SYMBOL_IN
    ELSE SYMBOL_OUT
    END AS token_first,
    CASE
    WHEN SYMBOL_IN < SYMBOL_OUT THEN SYMBOL_OUT
    ELSE SYMBOL_IN
    END AS token_second,
    COALESCE(amount_in_usd, 0) AS in_usd_amount,
    COALESCE(amount_out_usd, 0) AS out_usd_amount
    FROM
    near.defi.ez_dex_swaps
    WHERE
    PLATFORM = 'v1.jumbo_exchange.near'
    AND SYMBOL_IN IS NOT NULL
    AND SYMBOL_OUT IS NOT NULL
    ),
    daily_swap_volume AS (
    SELECT
    token_first,
    token_second,
    SUM(in_usd_amount + out_usd_amount) AS total_swap_volume,
    COUNT(*) AS swap_count
    FROM
    swaps
    WHERE
    swap_date >= '2024-01-01'
    GROUP BY
    token_first, token_second
    )
    SELECT
    token_first || '-' || token_second AS token_pair,
    ROUND(total_swap_volume, 0) AS total_volume,
    QueryRunArchived: QueryRun has been archived