SELECT

date_trunc('d', block_timestamp) as date,

count(DISTINCT trader) as DAU

FROM arbitrum.vertex.ez_perp_trades

GROUP BY date

,new as (

SELECT

date_trunc('d', first_tx) as date,

count(DISTINCT user) as new_user

FROM (

SELECT

trader as user,

min(block_timestamp) as first_tx

FROM arbitrum.vertex.ez_perp_trades

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 recurring_user,

100 * n_new_user / Dau as new_user_percent,

100 * recurring_user / Dau as recurring_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

LEFT JOIN new b using(date)

ORDER BY 1 DESC