COHORT_WEEK | ACTIVITY_WEEK | COHORT_SIZE | RETAINED_USERS | RETENTION_PERCENTAGE | |
|---|---|---|---|---|---|
1 | 2024-11-25 00:00:00.000 | 2024-11-25 00:00:00.000 | 2698147 | 2698147 | 100 |
2 | 2024-11-25 00:00:00.000 | 2024-12-02 00:00:00.000 | 2698147 | 804328 | 29.81 |
3 | 2024-11-25 00:00:00.000 | 2024-12-09 00:00:00.000 | 2698147 | 785184 | 29.1 |
4 | 2024-11-25 00:00:00.000 | 2024-12-16 00:00:00.000 | 2698147 | 746431 | 27.66 |
5 | 2024-11-25 00:00:00.000 | 2024-12-23 00:00:00.000 | 2698147 | 722028 | 26.76 |
6 | 2024-11-25 00:00:00.000 | 2024-12-30 00:00:00.000 | 2698147 | 709166 | 26.28 |
7 | 2024-11-25 00:00:00.000 | 2025-01-06 00:00:00.000 | 2698147 | 681989 | 25.28 |
8 | 2024-11-25 00:00:00.000 | 2025-01-13 00:00:00.000 | 2698147 | 671422 | 24.88 |
9 | 2024-11-25 00:00:00.000 | 2025-01-20 00:00:00.000 | 2698147 | 728736 | 27.01 |
10 | 2024-11-25 00:00:00.000 | 2025-01-27 00:00:00.000 | 2698147 | 628917 | 23.31 |
11 | 2024-11-25 00:00:00.000 | 2025-02-03 00:00:00.000 | 2698147 | 614865 | 22.79 |
12 | 2024-11-25 00:00:00.000 | 2025-02-10 00:00:00.000 | 2698147 | 609650 | 22.6 |
13 | 2024-11-25 00:00:00.000 | 2025-02-17 00:00:00.000 | 2698147 | 600453 | 22.25 |
14 | 2024-11-25 00:00:00.000 | 2025-02-24 00:00:00.000 | 2698147 | 585802 | 21.71 |
15 | 2024-11-25 00:00:00.000 | 2025-03-03 00:00:00.000 | 2698147 | 574109 | 21.28 |
16 | 2024-11-25 00:00:00.000 | 2025-03-10 00:00:00.000 | 2698147 | 560718 | 20.78 |
17 | 2024-11-25 00:00:00.000 | 2025-03-17 00:00:00.000 | 2698147 | 560720 | 20.78 |
18 | 2024-11-25 00:00:00.000 | 2025-03-24 00:00:00.000 | 2698147 | 540720 | 20.04 |
19 | 2024-11-25 00:00:00.000 | 2025-03-31 00:00:00.000 | 2698147 | 519317 | 19.25 |
20 | 2024-11-25 00:00:00.000 | 2025-04-07 00:00:00.000 | 2698147 | 506247 | 18.76 |
datavortexUser Retaintion last 6 month
Updated 2025-05-28
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
tx_signer AS user_address,
DATE_TRUNC('week', MIN(block_timestamp)) AS cohort_week
FROM near.core.fact_transactions
WHERE block_timestamp >= DATE_TRUNC('week', DATEADD(MONTH, -6, CURRENT_DATE))
GROUP BY tx_signer
),
retention AS (
SELECT
n.cohort_week,
DATE_TRUNC('week', t.block_timestamp) AS activity_week,
COUNT(DISTINCT t.tx_signer) AS retained_users
FROM near.core.fact_transactions t
JOIN new_users n
ON t.tx_signer = n.user_address
WHERE t.block_timestamp >= n.cohort_week
GROUP BY n.cohort_week, activity_week
),
cohort_sizes AS (
SELECT cohort_week, COUNT(DISTINCT user_address) AS cohort_size
FROM new_users
GROUP BY cohort_week
)
SELECT
r.cohort_week,
r.activity_week,
c.cohort_size,
r.retained_users,
ROUND((r.retained_users * 100.0) / c.cohort_size, 2) AS retention_percentage
FROM retention r
JOIN cohort_sizes c ON r.cohort_week = c.cohort_week
ORDER BY r.cohort_week, r.activity_week;
Last run: 18 days ago
...
378
27KB
206s