with blocks as (
select
'Ethereum' as network,
a.block_timestamp,
a.tx_count - (a.tx_count % 100) + 1 as tx_count,
DATEDIFF('millisecond', a.block_timestamp, b.block_timestamp) - (DATEDIFF('millisecond', a.block_timestamp, b.block_timestamp) % 1000) + 1000 as duration
from
ethereum.core.fact_blocks a
join ethereum.core.fact_blocks b on a.hash = b.parent_hash
where
a.tx_count > 0
)
select
network,
duration,
tx_count,
count(*) as cnt
from
blocks
group by
network, duration, tx_count
order by
network, duration, tx_count