SELECT

symbol

, token_address

, name

, price

, hour

FROM avalanche.price.ez_prices_hourly

WHERE hour = (SELECT MAX(hour)-- - INTERVAL '1 HOUR'

FROM avalanche.price.ez_prices_hourly

)

),

last_24hr_price AS (

SELECT

p.symbol

, p.token_address

, p.name

, p.price

, MAX(p.hour)

FROM avalanche.price.ez_prices_hourly p

JOIN latest_prices l

ON p.token_address = l.token_address

WHERE p.hour = l.hour - INTERVAL '24 HOURS'

GROUP BY 1, 2, 3, 4

ORDER BY p.token_address DESC

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_24hr_price p

ON l.token_address = p.token_address