with DAU_u as (

SELECT

date_trunc('{{granularity}}', block_timestamp) as date,

count(DISTINCT from_address) as DAU

FROM monad.testnet.fact_transactions

where date >= '2025-02-18'

GROUP BY date

,new as (

SELECT

date_trunc('{{granularity}}', first_tx) as date,

count(DISTINCT user) as new_user

FROM (

SELECT

from_address as user,

min(block_timestamp) as first_tx

FROM monad.testnet.fact_transactions

GROUP BY 1)

where date >= '2025-02-18'

GROUP BY 1)

SELECT

a.date,

Dau,

coalesce(new_user,0) as new_account,

sum(new_account) over (ORDER by date) as cum_account,

Dau - new_account as returning_account,

100 * new_account / Dau as new_account_percent,

100 * returning_account / Dau as returning_account_percent

-- avg(Dau)over(ORDER BY date) as "daily avg DAU",

-- AVG(Dau) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avarage_7_Dau,

-- avg(new_account)over(ORDER BY date) as "daily avg new account",

-- AVG(new_account) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avarage_7_new_account

FROM DAU_u a