SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS trading_day,

PLATFORM,

SYMBOL_IN,

COUNT(DISTINCT TX_HASH) AS trade_count,

SUM(AMOUNT_IN_USD) AS total_input_volume,

SUM(AMOUNT_OUT_USD) AS total_output_volume,

AVG(AMOUNT_IN_USD) AS avg_trade_size,

-- Safe liquidity velocity calculation

COALESCE(

NULLIF(SUM(AMOUNT_IN_USD), 0) /

NULLIF(COUNT(DISTINCT TRADER), 0),

0

) AS liquidity_velocity,

-- Robust price volatility calculation

COALESCE(

STDDEV(

CASE

WHEN AMOUNT_OUT_RAW > 0

THEN AMOUNT_IN_USD / AMOUNT_OUT_RAW

ELSE NULL

END

),

0

) AS price_volatility,

-- Trader diversity

COUNT(DISTINCT TRADER) AS unique_traders

FROM near.defi.ez_dex_swaps

WHERE

BLOCK_TIMESTAMP >= DATEADD(day, -90, CURRENT_DATE())

-- Optional: Filter out potential noise

AND AMOUNT_IN_USD > 0