SELECT

symbol_out as symbol,

token_out as token_address,

blockchain,

COUNT(DISTINCT tx_hash) as trade_count,

COUNT(DISTINCT trader) as unique_traders,

SUM(amount_out_usd) as volume_usd

FROM crosschain.defi.ez_dex_swaps

WHERE block_timestamp >= DATEADD(hour, -24, CURRENT_TIMESTAMP())

GROUP BY 1, 2, 3

HAVING trade_count >= 10 -- Minimum trades

AND unique_traders >= 5 -- Minimum unique traders

AND volume_usd >= 10000 -- Minimum USD volume

),

price_changes AS (

SELECT

p.symbol,

p.token_address,

p.blockchain,

p.hour,

p.price,

FIRST_VALUE(p.price) OVER (

PARTITION BY p.token_address, p.blockchain

ORDER BY p.hour DESC

) as latest_price,

FIRST_VALUE(p.price) OVER (

PARTITION BY p.token_address, p.blockchain

ORDER BY p.hour ASC

) as earliest_price

FROM crosschain.price.ez_prices_hourly p

INNER JOIN trading_volume tv

ON p.symbol = tv.symbol

AND p.token_address = tv.token_address

AND p.blockchain = tv.blockchain