with blocks as (
select
'Near' 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
near.core.fact_blocks a
join near.core.fact_blocks b on a.block_hash = b.prev_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