SELECT

block_id,

block_timestamp,

row_number() over (order by block_id, block_timestamp) as row_num,

tx_count

FROM terra.core.fact_blocks

),

B as (

SELECT

A.block_id,

A.block_timestamp,

datediff(second, A.block_timestamp, C.block_timestamp) as Tdiff,

A.tx_count

from A inner join A as C on A.row_num=C.row_num - 1

)

date_trunc('w',block_timestamp) as "date",

count(block_id) as "total_blocks",

sum(tx_count) as "tx",

avg(tx_count) as "avg_tx",

min(block_timestamp) as "min_timestamp",

max(block_timestamp) as "max_timestamp",

avg(Tdiff) as "avg_Tdiff",

min(Tdiff) as "min_Tdiff",

median(Tdiff) as "median_Tdiff",

max(Tdiff) as "max_Tdiff"

FROM B

WHERE Tdiff>= 0

group by 1