stat_date

,max(eth_price) as high

,min(eth_price) as low

,sum(case when true_number_rank=1 then eth_price else 0 end ) as open

,sum(case when false_number_rank=1 then eth_price else 0 end ) as close

from

SELECT

date_trunc('hour',BLOCK_TIMESTAMP) as stat_date

,round(LATEST_ANSWER_UNADJ/1e8,3) as eth_price

,row_number()over(partition by stat_date order by stat_date) as true_number_rank

,row_number()over(partition by stat_date order by stat_date desc ) as false_number_rank

from ethereum.chainlink.ez_oracle_feeds

where FEED_NAME='AAVE / USD'

) a

group by 1

order by 1