0xHaM-dNew vs Recurring Users
Updated 2025-01-22
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
›
⌄
with
txs as (
SELECT
BLOCK_TIMESTAMP,
TX_HASH,
FROM_ADDRESS,
FROM ink.core.fact_transactions
)
,DAU_u as (
SELECT
date_trunc('d', BLOCK_TIMESTAMP) as dt,
count(DISTINCT FROM_ADDRESS) as Active_users
FROM txs
GROUP BY 1
)
,new as (
SELECT
date_trunc('d', first_tx) as dt,
count(DISTINCT FROM_ADDRESS) as new_user
FROM (
SELECT
FROM_ADDRESS,
min(block_timestamp) as first_tx
FROM txs
GROUP BY 1
)
GROUP BY 1
)
SELECT
trunc(a.dt, 'd') as 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,
avg(Active_users)over(ORDER BY date) as "Avg AU",
QueryRunArchived: QueryRun has been archived