select

date_trunc('day', hour) as day,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where token_address is null

group by 1

),

daily as (

select

date_trunc(hour, block_timestamp) as date,

case

when extract(dow from date) = 0 then '7.Sunday'

when extract(dow from date) = 1 then '1.Monday'

when extract(dow from date) = 2 then '2.Tuesday'

when extract(dow from date) = 3 then '3.Wednesday'

when extract(dow from date) = 4 then '4.Thursday'

when extract(dow from date) = 5 then '5.Friday'

when extract(dow from date) = 6 then '6.Saturday'

end as days,

date_part(hour, date) as hours,

avg(price) * sum(tx_fee) as gas_fee

from arbitrum.core.fact_transactions join token_price on block_timestamp::date = day

where date >= '2022-06-01' and date < current_date

group by 1, 2, 3

select days, hours, avg(gas_fee) as fee

from daily

group by 1, 2

order by 1, 2 desc