SELECT

hour::date as date,

price,

ROW_NUMBER() OVER (PARTITION BY date ORDER BY hour) AS rn_open,

ROW_NUMBER() OVER (PARTITION BY date ORDER BY hour DESC) AS rn_close

FROM

aptos.price.ez_prices_hourly

WHERE

token_address = '0x1::aptos_coin::AptosCoin'

and hour::date >= current_date - interval '7 DAY'

SELECT

rp.date,

MAX(CASE WHEN rn_open = 1 THEN price END) AS "Open Price",

MAX(CASE WHEN rn_close = 1 THEN price END) AS "Close Price",

MAX(price) AS "Max Price",

MIN(price) AS "Min Price"

FROM

RankedPrices rp

GROUP BY

rp.date

ORDER BY

rp.date;