SELECT DATE_PART('hour', block_timestamp) as hour, DATE_TRUNC('day', block_timestamp) as day, gas_price,

CASE

WHEN hour >0 and hour <=8 THEN 'morning'

WHEN hour >8 and hour <=16 THEN 'afternoon'

ELSE 'evening'

END as time_of_day

FROM ethereum.core.fact_transactions

where block_timestamp >= '2022-08-01' and block_timestamp <= '2023-02-28'

select day, time_of_day, avg(gas_price)

from raw

GROUP BY 1, 2