crypto_gostmain-coffee
    Updated 2024-11-09
    -- WITH vol as (SELECT
    -- BLOCK_TIMESTAMP::date as day,
    -- PLATFORM,
    -- TX_HASH,
    -- SUM (AMOUNT_IN_USD + AMOUNT_OUT_USD) as volume
    -- FROM ethereum.defi.ez_dex_swaps
    -- QUALIFY
    -- ROW_NUMBER() OVER (PARTITION BY volume ORDER BY day ASC) = 1
    -- GROUP BY block_timestamp::date, tx_hash, platform
    -- LIMIT 10;
    -- )
    -- select volume

    WITH vol AS (
    SELECT
    BLOCK_TIMESTAMP::date AS day,
    PLATFORM,
    TX_HASH,
    SUM(AMOUNT_IN_USD + AMOUNT_OUT_USD) AS volume,
    ROW_NUMBER() OVER (PARTITION BY day, PLATFORM ORDER BY SUM(AMOUNT_IN_USD + AMOUNT_OUT_USD) DESC) AS rn
    FROM ethereum.defi.ez_dex_swaps
    WHERE BLOCK_TIMESTAMP >= DATEADD(month, -6, CURRENT_DATE) -- past 6 months
    GROUP BY BLOCK_TIMESTAMP::date, PLATFORM, TX_HASH
    )
    SELECT
    day,
    PLATFORM,
    TX_HASH,
    volume
    FROM vol
    WHERE rn = 1
    LIMIT 10;




    QueryRunArchived: QueryRun has been archived