SELECT

hour::date AS date,

price,

FIRST_VALUE(price) OVER (PARTITION BY hour::date ORDER BY hour) AS open_price, -- Open price

MAX(price) OVER (PARTITION BY hour::date) AS high_price, -- High price

MIN(price) OVER (PARTITION BY hour::date) AS low_price, -- Low price

LAST_VALUE(price) OVER (

PARTITION BY hour::date

ORDER BY hour ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS close_price -- Close price

FROM aptos.price.ez_prices_hourly

WHERE

symbol = 'APT'

SELECT

date AS "Date",

MAX(open_price) AS "Open", -- Open price

MAX(high_price) AS "High", -- High price

MAX(low_price) AS "Low", -- Low price

MAX(close_price) AS "Close" -- Close price

FROM

daily_data

GROUP BY

date

ORDER BY

date;