FIRST_WEEK | WEEK_NUMBER | COHORT_SIZE | RETAINED_USERS | RETENTION_RATE | |
|---|---|---|---|---|---|
1 | 2025-02-10 00:00:00.000 | 0 | 331 | 331 | 100 |
2 | 2025-02-10 00:00:00.000 | 1 | 138 | 138 | 41.69 |
3 | 2025-02-10 00:00:00.000 | 2 | 140 | 140 | 42.3 |
4 | 2025-02-10 00:00:00.000 | 3 | 117 | 117 | 35.35 |
5 | 2025-02-10 00:00:00.000 | 4 | 117 | 117 | 35.35 |
6 | 2025-02-10 00:00:00.000 | 5 | 106 | 106 | 32.02 |
7 | 2025-02-10 00:00:00.000 | 6 | 116 | 116 | 35.05 |
8 | 2025-02-10 00:00:00.000 | 7 | 87 | 87 | 26.28 |
9 | 2025-02-10 00:00:00.000 | 8 | 91 | 91 | 27.49 |
10 | 2025-02-10 00:00:00.000 | 9 | 90 | 90 | 27.19 |
11 | 2025-02-10 00:00:00.000 | 10 | 104 | 104 | 31.42 |
12 | 2025-02-10 00:00:00.000 | 11 | 89 | 89 | 26.89 |
13 | 2025-02-10 00:00:00.000 | 12 | 83 | 83 | 25.08 |
14 | 2025-02-17 00:00:00.000 | 0 | 1515 | 1515 | 100 |
15 | 2025-02-17 00:00:00.000 | 1 | 367 | 367 | 24.22 |
16 | 2025-02-17 00:00:00.000 | 2 | 328 | 328 | 21.65 |
17 | 2025-02-17 00:00:00.000 | 3 | 327 | 327 | 21.58 |
18 | 2025-02-17 00:00:00.000 | 4 | 309 | 309 | 20.4 |
19 | 2025-02-17 00:00:00.000 | 5 | 347 | 347 | 22.9 |
20 | 2025-02-17 00:00:00.000 | 6 | 309 | 309 | 20.4 |
Abbas_ra21Cohort retention
Updated 2025-05-11
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 first_txn_week AS (
SELECT
from_address as user_address,
DATE_TRUNC('week', block_timestamp) as first_week
FROM swell.core.fact_transactions
WHERE block_timestamp >= DATEADD('week', -12, CURRENT_DATE())
GROUP BY 1, 2
HAVING first_week = MIN(DATE_TRUNC('week', block_timestamp))
),
weekly_activity AS (
SELECT
from_address as user_address,
DATE_TRUNC('week', block_timestamp) as activity_week
FROM swell.core.fact_transactions
WHERE block_timestamp >= DATEADD('week', -12, CURRENT_DATE())
GROUP BY 1, 2
),
cohort_retention AS (
SELECT
f.first_week,
w.activity_week,
DATEDIFF('week', f.first_week, w.activity_week) as week_number,
COUNT(DISTINCT f.user_address) as cohort_size,
COUNT(DISTINCT w.user_address) as retained_users,
ROUND(COUNT(DISTINCT w.user_address) * 100.0 /
FIRST_VALUE(COUNT(DISTINCT w.user_address))
OVER (PARTITION BY f.first_week ORDER BY w.activity_week), 2) as retention_rate
FROM first_txn_week f
LEFT JOIN weekly_activity w
ON f.user_address = w.user_address
AND w.activity_week >= f.first_week
GROUP BY 1, 2, 3
)
Last run: 21 days ago
91
4KB
1s