block_id,

MIN(block_timestamp) as earliest_time,

ROW_NUMBER() OVER (ORDER BY block_id) AS rownum

from solana.core.fact_transactions

group by 1

having earliest_time >= CURRENT_DATE - 47

order by 1 asc),

block_per_sec as (select

DATE(curr.earliest_time) as dayz,

DATEDIFF(second, curr.earliest_time, prev.earliest_time) as timediff

FROM block curr

INNER JOIN block prev

ON curr.rownum = prev.rownum - 1)

select

dayz,

MAX(timediff) as max_time_per_block,

MIN(timediff) as min_time_per_block,

AVG(timediff) as avg_time_per_block,

MEDIAN(timediff) as median_time_per_block

from block_per_sec

group by 1