WITH
dates as (
select
distinct BLOCK_NUMBER,
block_timestamp
from ethereum.core.fact_blocks
),
final_data as (
SELECT
x.BLOCK_NUMBER,
y.block_timestamp, y.tx_count
from ethereum.core.fact_transactions as x
join ethereum.core.fact_blocks as y
on x.BLOCK_NUMBER=y.BLOCK_NUMBER
)
select
trunc(block_timestamp,'week') as week,
count(distinct BLOCK_NUMBER ) as num_blocks,
count(distinct TX_COUNT) as num_txs
from final_data
--where week >= '2020-01-01'
group by 1
order by 1 DESC
limit 8