Quarter | New Users | Returning Users | Quarterly Users | |
|---|---|---|---|---|
1 | 2019-04-01 00:00:00.000 | 495 | 0 | 495 |
2 | 2019-07-01 00:00:00.000 | 631002 | 276 | 631278 |
3 | 2019-10-01 00:00:00.000 | 4189834 | 18894 | 4208728 |
4 | 2020-01-01 00:00:00.000 | 2802103 | 121302 | 2923405 |
5 | 2020-04-01 00:00:00.000 | 1700407 | 96906 | 1797313 |
6 | 2020-07-01 00:00:00.000 | 715396 | 65391 | 780787 |
7 | 2020-10-01 00:00:00.000 | 574828 | 68202 | 643030 |
8 | 2021-01-01 00:00:00.000 | 401986 | 127865 | 529851 |
9 | 2021-04-01 00:00:00.000 | 534139 | 80906 | 615045 |
10 | 2021-07-01 00:00:00.000 | 500889 | 97045 | 597934 |
11 | 2021-10-01 00:00:00.000 | 2426179 | 288418 | 2714597 |
12 | 2022-01-01 00:00:00.000 | 1582605 | 734548 | 2317153 |
13 | 2022-04-01 00:00:00.000 | 1856350 | 653336 | 2509686 |
14 | 2022-07-01 00:00:00.000 | 770344 | 401008 | 1171352 |
15 | 2022-10-01 00:00:00.000 | 744665 | 247207 | 991872 |
16 | 2023-01-01 00:00:00.000 | 997864 | 271091 | 1268955 |
17 | 2023-04-01 00:00:00.000 | 3816242 | 277800 | 4094042 |
18 | 2023-07-01 00:00:00.000 | 2023842 | 367530 | 2391372 |
19 | 2023-10-01 00:00:00.000 | 2057581 | 305264 | 2362845 |
20 | 2024-01-01 00:00:00.000 | 4991023 | 283898 | 5274921 |
i_danKaia: New Vs Returning Users
Updated 2025-02-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
›
⌄
WITH first_tx AS (
SELECT
from_address
, MIN(block_timestamp) AS first_quarter
FROM kaia.core.fact_transactions
GROUP BY 1
),
user_activity AS (
SELECT
t.from_address
, DATE_TRUNC('quarter', t.block_timestamp) AS activity_quarter
, DATE_TRUNC('quarter', f.first_quarter) AS first_tx_quarter
FROM kaia.core.fact_transactions t
JOIN first_tx f ON t.from_address = f.from_address
)
SELECT
activity_quarter AS "Quarter"
, COUNT(DISTINCT CASE WHEN activity_quarter = first_tx_quarter THEN from_address END) AS "New Users"
, COUNT(DISTINCT CASE WHEN activity_quarter > first_tx_quarter THEN from_address END) AS "Returning Users"
, COUNT(DISTINCT from_address) AS "Quarterly Users" --
FROM user_activity
GROUP BY 1
ORDER BY 1
Last run: about 1 month ago
24
1KB
201s