SELECT

hour::date AS date,

AVG(price) AS avg_price

FROM avalanche.price.ez_prices_hourly

WHERE symbol ILIKE 'WAVAX'

GROUP BY 1

),

price_snapshots AS (

SELECT

MAX(CASE WHEN date = CURRENT_DATE THEN avg_price END) AS current_price,

MAX(CASE WHEN date = CURRENT_DATE - INTERVAL '1 day' THEN avg_price END) AS price_24h_ago,

MAX(CASE WHEN date = CURRENT_DATE - INTERVAL '7 day' THEN avg_price END) AS price_7d_ago,

MAX(CASE WHEN date = CURRENT_DATE - INTERVAL '30 day' THEN avg_price END) AS price_30d_ago

FROM daily_avg_prices

SELECT

current_price,

price_24h_ago,

price_7d_ago,

price_30d_ago,

CASE

WHEN price_24h_ago > 0 THEN

CASE

WHEN ((current_price - price_24h_ago) / price_24h_ago) * 100 > 0 THEN '⬆️ ' || ROUND(((current_price - price_24h_ago) / price_24h_ago) * 100, 2)

WHEN ((current_price - price_24h_ago) / price_24h_ago) * 100 < 0 THEN '⬇️ ' || ROUND(((current_price - price_24h_ago) / price_24h_ago) * 100, 2)

ELSE '0%'

END

ELSE NULL

END AS growth_24h,

CASE

WHEN price_7d_ago > 0 THEN

CASE

WHEN ((current_price - price_7d_ago) / price_7d_ago) * 100 > 0 THEN '⬆️ ' || ROUND(((current_price - price_7d_ago) / price_7d_ago) * 100, 2)

WHEN ((current_price - price_7d_ago) / price_7d_ago) * 100 < 0 THEN '⬇️ ' || ROUND(((current_price - price_7d_ago) / price_7d_ago) * 100, 2)

ELSE '0%'