new as (

SELECT

distinct from_address as user,

min(block_timestamp) as date

from avalanche.core.fact_transactions

where block_timestamp::date>='{{StartDate}}' and block_timestamp::date<='{{EndDate}}'

group by 1

,

active_users as (

SELECT

date_trunc('day',block_timestamp) as date,

count(distinct from_address) as count_users

from avalanche.core.fact_transactions

where block_timestamp::date>='{{StartDate}}' and block_timestamp::date<='{{EndDate}}'

group by 1

select

trunc(date,'{{Timestamp}}') as "Timestamp",

sum(count_users) as "Active users",

AVG("Active users") OVER (ORDER BY "Timestamp" ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "7MA",

sum("Active users") over (order by "Timestamp") as "Total active users"

from active_users

group by 1

order by 1 asc