SELECT

date_trunc('week', block_timestamp) as date,

count(DISTINCT origin_from_address) as DAU

FROM {{chain}}.core.ez_decoded_event_logs

where block_timestamp::date >= '2022-10-01'

and event_name IN ('LiFiGenericSwapCompleted','LiFiTransferStarted')

GROUP BY date

,new as (

SELECT

date_trunc('week', first_tx) as date,

count(DISTINCT user) as new_user

FROM (

SELECT

origin_from_address as user,

min(block_timestamp) as first_tx

FROM {{chain}}.core.ez_decoded_event_logs

where block_timestamp::date >= '2022-10-01'

and event_name IN ('LiFiGenericSwapCompleted','LiFiTransferStarted')

GROUP BY 1)

GROUP BY 1)

SELECT

a.date,

Dau,

coalesce(new_user,0) as n_new_user,

sum(n_new_user) over (ORDER by date) as cum_users,

Dau - n_new_user as returning_user,

100 * n_new_user / Dau as new_user_percent,

100 * returning_user / Dau as returning_user_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(n_new_user)over(ORDER BY date) as "daily avg new user",

AVG(n_new_user) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avarage_7_new_user

FROM DAU_u a