-- filtered after 2022-05-01 since avax data before seems odd.
with table_avax as (select date_trunc('hour', block_timestamp) as date, avg(tx_count) as avg_tx_count from avalanche.core.fact_blocks
where to_date(block_timestamp) >= '2022-05-01'
group by date),
table_eth as
(select date_trunc('hour', block_timestamp) as date, avg(tx_count) as avg_tx_count from ethereum.core.fact_blocks
where to_date(block_timestamp) >= '2022-05-01'
group by date)
select a.date, a.avg_tx_count as eth_avg_tx_count_per_block, nvl(b.avg_tx_count,0) as avax_avg_tx_count_per_block from table_eth a
left join table_avax b
on a.date = b.date