SELECT

block_timestamp,

from_address AS user

FROM kaia.core.fact_transactions

),

first_transaction AS (

SELECT

MIN(block_timestamp) AS first_day,

user

FROM transactions

GROUP BY user

SELECT

TRUNC(first_day, 'd') AS date,

COUNT(DISTINCT user) AS new_users,

SUM(COUNT(DISTINCT user)) OVER (ORDER BY TRUNC(first_day, 'd') ASC) AS cumulative_new_users,

AVG(COUNT(DISTINCT user)) OVER (ORDER BY TRUNC(first_day, 'd') ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg_new_users

FROM first_transaction

GROUP BY TRUNC(first_day, 'd')

ORDER BY date DESC;