SELECT DISTINCT

CASE

WHEN SYMBOL_IN = 'WETH' THEN TOKEN_OUT

WHEN SYMBOL_OUT = 'WETH' THEN TOKEN_IN

END AS token_address

FROM base.defi.ez_dex_swaps

WHERE platform = 'uniswap-v3'

AND (SYMBOL_IN = 'WETH' OR SYMBOL_OUT = 'WETH')

),

price_data AS (

-- Get hourly price data for tokens paired with WETH

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 weth_pairs)

),

returns AS (

-- Compute hourly returns for each token

SELECT

token_address,

hour,

(price - prev_price) / prev_price AS hourly_return

FROM price_data

WHERE prev_price IS NOT NULL

),

returns_with_cap AS (

-- Compute 99th percentile and cap returns in one step

SELECT

token_address,

hour,

LEAST(

hourly_return,