select block_id as first_block_number, block_timestamp

from terra.core.fact_transactions

where block_timestamp >= CURRENT_DATE - {{Past_X_Days}}

),

base1 as (

select first_block_number + 1 as second_block_number, block_timestamp

from base

),

info as (

select ft.block_id as bn, ft.block_timestamp as TIME, timestampdiff('SECOND', base1.block_timestamp, base.block_timestamp) AS time_diff

from terra.core.fact_transactions ft

inner join base on base.first_block_number = ft.block_id

inner join base1 on base1.second_block_number = ft.block_id)

date_trunc('week', TIME) as period,

sum(time_diff)/count(bn) as average_block_time

from info

group by period