symbol,

price,

avg(price) over (partition by symbol order by dt rows between 7 preceding and current row) as price_ma,

CASE WHEN symbol = 'Solana' THEN 445685811 * price

WHEN symbol = 'Avalanche' THEN 377910055 * price

WHEN symbol = 'Sui' THEN 1295901469 * price

WHEN symbol = 'Aptos' THEN 398807598 * price

WHEN symbol = 'Polygon' THEN 9902235985 * price

END as mcap

FROM (

SELECT

dt,

CASE

WHEN symbol = 'APT' THEN 'Aptos'

WHEN symbol = 'AVAX' THEN 'Avalanche'

WHEN symbol = 'MATIC' THEN 'Polygon'

WHEN symbol = 'SOL' THEN 'Solana'

WHEN symbol = 'SUI' THEN 'Sui'

end as symbol,

price

FROM (

SELECT

date_trunc('day',hour) as dt,

symbol,

avg(price) as price

FROM crosschain.price.ez_prices_hourly

WHERE symbol in ('SOL','AVAX','MATIC')

AND hour >= CURRENT_DATE - INTERVAL '365 days'

AND price != 0

GROUP BY 1,2

UNION ALL

TO_TIMESTAMP(value[0]::string) as dt,