WITH

days AS (

SELECT

DATE_TRUNC('day', block_timestamp) as day,

from_address as user_address

FROM

avalanche.core.fact_transactions

WHERE block_timestamp > current_timestamp - interval '{{time_frame}} days'

GROUP BY

day,

from_address

),

day AS (

SELECT

DATE_TRUNC('day', day) as day,

user_address,

COUNT(*) as num_days

FROM

days

GROUP BY

day,

user_address

HAVING

num_days >= 1

),

active_users AS (

SELECT

day,

COUNT(*) as num_active_users,

AVG(num_active_users) OVER(ORDER BY day) AS avg_num_active_users

FROM

day

GROUP BY

day