with avax_burned as (select block_number, sum(tx_fee) as total_avax_burned from avalanche.core.fact_transactions
where block_timestamp >= '2022-6-20'
group by block_number),
joined as (select
a.*,
b.block_timestamp
from avalanche.core.fact_blocks b, avax_burned a
where b.block_number = a.block_number)
select
date_trunc('hour', block_timestamp) as hour,
sum(total_avax_burned) as hourly_avax_burned
from joined
group by hour