time,

blocks,

avg(blocks) over (order by time rows between 7 preceding and current row) as weekly_avg

from (

select

date_trunc('day',block_timestamp) as time,

count(distinct block_id) as blocks

from cosmos.core.fact_blocks

where block_timestamp > '2021-01-01'

group by 1