SELECT

symbol,

hour,

price,

ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS rn

FROM optimism.price.ez_prices_hourly

),

latest_prices AS (

SELECT

symbol,

price AS latest_price

FROM prices

WHERE rn = 1

),

recent_data AS (

SELECT

block_timestamp,

tx_hash,

origin_from_address,

COALESCE(amount_in, 0) AS amount_in, -- The amount of tokens swapped

symbol_in AS symbol

FROM optimism.defi.ez_dex_swaps

WHERE block_timestamp >= CURRENT_TIMESTAMP() - INTERVAL '1 DAY'

r.symbol,

SUM(r.amount_in) AS total_amount_in,

l.latest_price,

SUM(r.amount_in * l.latest_price) AS total_value_in_usd

FROM recent_data r

JOIN latest_prices l

ON r.symbol = l.symbol