yasminPNG Token Swap Transaction and Volume Tracker copy
    Updated 2024-06-17
    WITH tab1 AS (
    SELECT
    SUM(amount_in_usd) AS sell_volume,
    COUNT(DISTINCT tx_hash) AS sell_tx
    FROM
    avalanche.defi.ez_dex_swaps
    WHERE
    token_in = LOWER('0x60781c2586d68229fde47564546784ab3faca982')
    ),
    tab2 AS (
    SELECT
    SUM(amount_out_usd) AS buy_volume,
    COUNT(DISTINCT tx_hash) AS buy_tx
    FROM
    avalanche.defi.ez_dex_swaps
    WHERE
    token_out = LOWER('0x60781c2586d68229fde47564546784ab3faca982')
    )
    SELECT
    COALESCE(sell_volume, 0) AS sell_volume,
    COALESCE(sell_tx, 0) AS sell_tx,
    COALESCE(buy_volume, 0) AS buy_volume,
    COALESCE(buy_tx, 0) AS buy_tx,
    COALESCE(sell_volume, 0) + COALESCE(buy_volume, 0) AS total_volume,
    COALESCE(sell_tx, 0) + COALESCE(buy_tx, 0) AS total_transactions
    FROM
    tab1
    CROSS JOIN tab2;

    QueryRunArchived: QueryRun has been archived