adriaparcerisasnear outlets of increasing trading volume 2
    Updated 5 hours ago
    WITH base_data AS (
    SELECT
    trunc(block_timestamp, 'day') AS trade_date,
    tx_hash,
    trader,
    token_in_contract,
    symbol_in,
    token_out_contract,
    symbol_out,
    amount_in,
    amount_out,
    amount_in_usd,
    amount_out_usd,
    pool_id
    FROM near.defi.ez_dex_swaps
    WHERE block_timestamp >= CURRENT_DATE - INTERVAL '{{period}} DAYS'
    ),

    pricing_data AS (
    SELECT
    trunc(hour, 'day') AS price_date,
    token_address,
    AVG(price) AS avg_price
    FROM near.price.ez_prices_hourly
    GROUP BY 1, 2
    ),

    enriched_data AS (
    SELECT
    b.*,
    COALESCE(b.amount_in_usd, b.amount_in * p_in.avg_price) AS final_amount_in_usd,
    COALESCE(b.amount_out_usd, b.amount_out * p_out.avg_price) AS final_amount_out_usd
    FROM base_data b
    LEFT JOIN pricing_data p_in
    ON b.trade_date = p_in.price_date
    AND b.token_in_contract = p_in.token_address
    Last run: about 5 hours ago
    TRADE_DATE
    CATEGORY
    TOTAL_SWAPS
    UNIQUE_TRADERS
    TOTAL_VOLUME_USD
    AVG_TRADE_VOLUME
    MAX_TRADE_VOLUME
    MEDIAN_TRADE_VOLUME
    1
    2025-06-01 00:00:00.000Bot16463471223806.0418285764.7687770223734.61772520919.856621498
    2
    2025-06-01 00:00:00.000Stablecoin1254858303452.951683217137.49567362243968.4728214.586037
    3
    2025-06-01 00:00:00.000Regular User1318594165960.043049454133.087444306106896.3822100384.701252359
    4
    2025-06-01 00:00:00.000Meme Cooking5985350092.33057966994.1585161273161.623086978110.087856337
    5
    2025-05-31 00:00:00.000Bot19581481994079.2071815879.3442307495633.17619604825.017853843
    6
    2025-05-31 00:00:00.000Stablecoin882388299181.565085101196.95955568549659.5316212998.548699641
    7
    2025-05-31 00:00:00.000Regular User150950985926.78169802961.07091805114650.7537746825.908714473
    8
    2025-05-31 00:00:00.000Meme Cooking5804946687.27770314889.439229316918.466719291113.293887378
    9
    2025-05-30 00:00:00.000Bot22930492612138.2319812481.155069815204.08525067430.816085
    10
    2025-05-30 00:00:00.000Stablecoin1202446394623.456841042199.00325609711980.82506216412.157727992
    11
    2025-05-30 00:00:00.000Regular User1781523119503.44760437664.98284263415807.83328377711.73969762
    12
    2025-05-30 00:00:00.000Meme Cooking5725945339.44015574189.781069615362.169246778112.229518287
    13
    2025-05-29 00:00:00.000Bot19529482046400.7775259776.0941798063803.5253606427.960845172
    14
    2025-05-29 00:00:00.000Stablecoin1113500519895.784782618278.465872942100012.7135688316.579526
    15
    2025-05-29 00:00:00.000Regular User2062620433825.508473737211.004624744296539.0761851411.671075605
    16
    2025-05-29 00:00:00.000Meme Cooking5876241474.3876072181.322328642153.910466701108.571063267
    17
    2025-05-28 00:00:00.000Bot17216461759501.9787463578.6721206683865.10215268727.864547447
    18
    2025-05-28 00:00:00.000Stablecoin1502861331398.281704436150.08980149726661.2577743253.350490065
    19
    2025-05-28 00:00:00.000Regular User1848725263072.226890598159.4377132679519.5955224426.851497661
    20
    2025-05-28 00:00:00.000Meme Cooking6055845763.39005414389.03383279355.06093269112.828339387
    ...
    360
    37KB
    714s