FIRST_WEEK | WEEK_NUMBER | COHORT_SIZE | RETAINED_USERS | RETENTION_RATE | |
|---|---|---|---|---|---|
1 | 2025-01-27 00:00:00.000 | 0 | 1052 | 1052 | 100 |
2 | 2025-01-27 00:00:00.000 | 1 | 282 | 282 | 26.81 |
3 | 2025-01-27 00:00:00.000 | 2 | 269 | 269 | 25.57 |
4 | 2025-01-27 00:00:00.000 | 3 | 249 | 249 | 23.67 |
5 | 2025-01-27 00:00:00.000 | 4 | 210 | 210 | 19.96 |
6 | 2025-01-27 00:00:00.000 | 5 | 194 | 194 | 18.44 |
7 | 2025-01-27 00:00:00.000 | 6 | 175 | 175 | 16.63 |
8 | 2025-01-27 00:00:00.000 | 7 | 184 | 184 | 17.49 |
9 | 2025-01-27 00:00:00.000 | 8 | 209 | 209 | 19.87 |
10 | 2025-01-27 00:00:00.000 | 9 | 184 | 184 | 17.49 |
11 | 2025-01-27 00:00:00.000 | 10 | 168 | 168 | 15.97 |
12 | 2025-01-27 00:00:00.000 | 11 | 175 | 175 | 16.63 |
13 | 2025-01-27 00:00:00.000 | 12 | 145 | 145 | 13.78 |
14 | 2025-02-03 00:00:00.000 | 0 | 1753 | 1753 | 100 |
15 | 2025-02-03 00:00:00.000 | 1 | 477 | 477 | 27.21 |
16 | 2025-02-03 00:00:00.000 | 2 | 395 | 395 | 22.53 |
17 | 2025-02-03 00:00:00.000 | 3 | 340 | 340 | 19.4 |
18 | 2025-02-03 00:00:00.000 | 4 | 327 | 327 | 18.65 |
19 | 2025-02-03 00:00:00.000 | 5 | 301 | 301 | 17.17 |
20 | 2025-02-03 00:00:00.000 | 6 | 304 | 304 | 17.34 |
Abbas_ra21Cohort retention
Updated 21 hours 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 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: about 21 hours ago
91
4KB
2s