SELECT

symbol,

hour,

price,

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

FROM polygon.price.ez_prices_hourly

WHERE symbol IN ('USDT', 'USDC', 'EURS', 'DAI', 'UST', 'BUSD', 'TUSD')

AND hour >= '{{ start_date }}'

AND hour < '{{ end_date }}'

),

latest_prices AS (

SELECT

symbol,

price AS price_previous_hour

FROM prices

WHERE rn = 1

),

price_7d_ago AS (

SELECT

symbol,

price AS price_7d_ago

FROM prices

WHERE hour = (SELECT MAX(hour) - INTERVAL '7 DAY' FROM prices WHERE symbol = prices.symbol)

l.symbol,

l.price_previous_hour AS price_previous_hour,

p.price_7d_ago AS price_7d_ago,

ROUND(((l.price_previous_hour - p.price_7d_ago) / p.price_7d_ago) * 100, 1) AS percentage_change_7d

FROM latest_prices l

JOIN price_7d_ago p

ON l.symbol = p.symbol

ORDER BY l.symbol;