SELECT DISTINCT TOKEN_OUT AS token_address

FROM base.defi.ez_dex_swaps

WHERE SYMBOL_IN = 'WETH' AND platform = 'uniswap-v3'

UNION ALL

SELECT DISTINCT TOKEN_IN AS token_address

FROM base.defi.ez_dex_swaps

WHERE SYMBOL_OUT = 'WETH' AND platform = 'uniswap-v3'

),

v3_assets AS (

SELECT DISTINCT token_address FROM weth_pairs

),

price_data AS (

SELECT

token_address,

hour,

price,

LAG(price) OVER (PARTITION BY token_address ORDER BY hour) AS prev_price

FROM base.price.ez_prices_hourly

WHERE token_address IN (SELECT token_address FROM v3_assets)

),

latest_price AS (

SELECT token_address, price AS latest_price, hour

FROM base.price.ez_prices_hourly

QUALIFY ROW_NUMBER() OVER (PARTITION BY token_address ORDER BY hour DESC) = 1

),

returns AS (

SELECT

token_address,

hour,

(price - prev_price) / prev_price AS hourly_return

FROM price_data