SELECT

d.DATE_HOUR AS Hour,

p.CLOSE,

LAG(p.CLOSE, 1) OVER (ORDER BY d.DATE_HOUR) AS PreviousClose,

ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('day', d.DATE_HOUR) ORDER BY d.DATE_HOUR) AS HourlyRank -- Identifica la primera hora de cada día

FROM crosschain.core.dim_date_hours d

LEFT JOIN BITCOIN.price.fact_hourly_token_prices p ON d.DATE_HOUR = p.HOUR

WHERE d.DATE_HOUR >= DATEADD(day, -30, CURRENT_TIMESTAMP()) -- Filtra para los últimos 30 días

),

PercentChanges AS (

SELECT

Hour,

CASE WHEN HourlyRank = 1 THEN 0 ELSE (CLOSE - PreviousClose) / PreviousClose * 100 END AS PercentChange -- Excluye el primer dato del día

FROM HourlyData

WHERE PreviousClose IS NOT NULL -- Asegúrate de tener un precio previo para calcular el cambio porcentual

),

CumulativeSums AS (

SELECT

Hour,

SUM(CASE WHEN CONVERT_TIMEZONE('UTC', 'America/New_York', Hour)::time BETWEEN '08:00' AND '18:00' THEN PercentChange ELSE 0 END) OVER (ORDER BY Hour) AS CumulativeSumUS,

SUM(CASE WHEN CONVERT_TIMEZONE('UTC', 'Europe/Brussels', Hour)::time BETWEEN '08:00' AND '18:00' THEN PercentChange ELSE 0 END) OVER (ORDER BY Hour) AS CumulativeSumEU,

SUM(CASE WHEN CONVERT_TIMEZONE('UTC', 'Asia/Seoul', Hour)::time BETWEEN '08:00' AND '18:00' THEN PercentChange ELSE 0 END) OVER (ORDER BY Hour) AS CumulativeSumAPAC

FROM PercentChanges

Hour,

CumulativeSumUS,

CumulativeSumEU,

CumulativeSumAPAC

FROM CumulativeSums

ORDER BY Hour;