SELECT

symbol

, token_address

, name

, price

, hour

FROM avalanche.price.ez_prices_hourly

WHERE hour = (SELECT MAX(hour)

FROM avalanche.price.ez_prices_hourly

)

),

Last_7d_prices AS (

SELECT

symbol,

token_address,

name,

price,

hour

FROM avalanche.price.ez_prices_hourly

WHERE hour = (

SELECT MAX(hour)

FROM avalanche.price.ez_prices_hourly p

WHERE p.token_address = avalanche.price.ez_prices_hourly.token_address

AND hour <= CURRENT_DATE - INTERVAL '7 DAYS'

)

SELECT

l.symbol AS "Token"

, ROUND((l.price - p.price)/p.price * 100, 2) AS "Price Change"

, CASE WHEN "Price Change" > 0 THEN '🟢' ELSE '🔴' END AS "Change"

, l.token_address AS "Address"

FROM latest_prices l

JOIN Last_7d_prices p

ON l.token_address = p.token_address