SELECT

symbol

, token_address

, name

, price

, hour

FROM aptos.price.ez_prices_hourly

WHERE hour = (SELECT MAX(hour)

FROM aptos.price.ez_prices_hourly)

),

Last_30d_prices AS (

SELECT

symbol,

token_address,

name,

price,

hour

FROM aptos.price.ez_prices_hourly e

WHERE hour = (SELECT

MAX(hour)

FROM aptos.price.ez_prices_hourly p

WHERE p.token_address = e.token_address

AND hour = CURRENT_DATE - INTERVAL '30 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_30d_prices p

ON l.token_address = p.token_address