with block as (

select block_timestamp,

lead(block_timestamp) over(order by block_id) as next_block_time,

datediff('s',block_timestamp,next_block_time) as diff,

tx_count

from terra.core.fact_blocks

),

info as (

select

date_trunc('week', block_timestamp) as "Day",

avg(diff) as "block age",

avg(tx_count) as "Block tx",

avg("Block tx") over (order by "Day", "Day" rows between 6 preceding and current row) as "MA7 block tx"

from block

group by 1

),

avg_info as (

select

avg("block age") as "AVG block age",

avg("Block tx") as "AVG block tx"

from info

select *

from info, avg_info

order by "Day"