rajsFlow New and Active Users
Updated 2022-07-19Copy Reference Fork
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 new_users as
(
SELECT
joined_date as date,
count(distinct user) as new_users
FROM
(
SELECT
authorizers[0] as user,
min(date_trunc('week', block_timestamp)) as joined_date
from flow.core.fact_transactions
where block_timestamp >= '2022-04-01'
group by 1
-- limit 3
)
group by 1
)
,
active_users as
(
SELECT
date_trunc('week', block_timestamp) as date,
count(distinct authorizers[0]) as active_users
from flow.core.fact_transactions
where block_timestamp >= '2022-04-01'
group by 1
)
SELECT
a.date,
active_users,
new_users,
new_users / active_users * 100 as new_users_pct,
active_users - new_users as returning_users,
(active_users - new_users) / active_users * 100 as returning_users_pct
Run a query to Download Data