0xHaM-dNew vs recurring Users copy copy
Updated 2024-06-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from New vs recurring Users copy @ https://flipsidecrypto.xyz/edit/queries/6f298bfb-dc45-4726-b63c-6573761c102a
-- forked from New vs recurring Users @ https://flipsidecrypto.xyz/edit/queries/f5903827-d83f-47b0-af2d-3dab35d07b96
-- forked from https://flipsidecrypto.xyz/SocioAnalytica/q/wdRA-s_GBSEi/new-vs-recurring-traders
with DAU_u as (
SELECT
date_trunc('d', block_timestamp) as date,
count(DISTINCT FROM_ADDRESS) as Active_users
FROM blast.core.fact_transactions
GROUP BY date
)
,new as (
SELECT
date_trunc('d', first_tx) as date,
count(DISTINCT user) as new_user
FROM (
SELECT
FROM_ADDRESS as user,
min(block_timestamp) as first_tx
FROM blast.core.fact_transactions
GROUP BY 1)
GROUP BY 1)
SELECT
a.date,
Active_users,
coalesce(new_user,0) as n_new_user,
sum(n_new_user) over (ORDER by date) as cum_users,
Active_users - n_new_user as recurring_user,
100 * n_new_user / Active_users as new_user_percent,
100 * recurring_user / Active_users as recurring_user_percent,
avg(Active_users)over(ORDER BY date) as "Avg AU",
avg(n_new_user)over(ORDER BY date) as "Avg New User",
sum(n_new_user) over (ORDER BY date) as cum_new_user
FROM DAU_u a
QueryRunArchived: QueryRun has been archived