total users last 30D | new users last 30D | returning users last 30D | new users % of total | returning users % of total | |
|---|---|---|---|---|---|
1 | 60966 | 54533 | 6433 | 89.4 | 10.6 |
datavortexmetropolitan-brown
Updated 5 days ago
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 user_first_swaps AS (
SELECT
origin_from_address,
MIN(block_timestamp) AS first_swap_time
FROM
kaia.defi.ez_dex_swaps
GROUP BY
origin_from_address
),
new_users_last_30 AS (
SELECT
COUNT(DISTINCT origin_from_address) AS new_users_count_30d
FROM
user_first_swaps
WHERE
first_swap_time > CURRENT_TIMESTAMP - INTERVAL '30 days'
),
all_users_last_30 AS (
SELECT
COUNT(DISTINCT origin_from_address) AS all_users_count_30d
FROM
kaia.defi.ez_dex_swaps
WHERE
block_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days'
)
SELECT
a.all_users_count_30d AS "total users last 30D",
n.new_users_count_30d AS "new users last 30D",
a.all_users_count_30d - n.new_users_count_30d AS "returning users last 30D",
ROUND(
(n.new_users_count_30d * 100.0) / NULLIF(a.all_users_count_30d, 0),
1
) AS "new users % of total",
ROUND(
(
(a.all_users_count_30d - n.new_users_count_30d) * 100.0
Last run: 5 days ago
1
30B
10s