COHORT_MONTH | COHORTMONTH | Cohort Month | ACTIVITY_MONTH | ACTIVITYMONTH | Activity Month | RETAINED_USERS | |
|---|---|---|---|---|---|---|---|
1 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 3 |
2 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 3 |
3 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2025-01-01 00:00:00.000 | 3 | Month 3 | 3 |
4 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2025-02-01 00:00:00.000 | 4 | Month 4 | 3 |
5 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2025-03-01 00:00:00.000 | 5 | Month 5 | 3 |
6 | 2024-11-01 00:00:00.000 | 1 | Month 1 | 2025-04-01 00:00:00.000 | 6 | Month 6 | 3 |
7 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 2024-12-01 00:00:00.000 | 1 | Month 2 | 1586 |
8 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 2025-01-01 00:00:00.000 | 2 | Month 3 | 842 |
9 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 2025-02-01 00:00:00.000 | 3 | Month 4 | 523 |
10 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 2025-03-01 00:00:00.000 | 4 | Month 5 | 472 |
11 | 2024-12-01 00:00:00.000 | 2 | Month 2 | 2025-04-01 00:00:00.000 | 5 | Month 6 | 63 |
12 | 2025-01-01 00:00:00.000 | 3 | Month 3 | 2025-01-01 00:00:00.000 | 1 | Month 3 | 8368 |
13 | 2025-01-01 00:00:00.000 | 3 | Month 3 | 2025-02-01 00:00:00.000 | 2 | Month 4 | 1650 |
14 | 2025-01-01 00:00:00.000 | 3 | Month 3 | 2025-03-01 00:00:00.000 | 3 | Month 5 | 1306 |
15 | 2025-01-01 00:00:00.000 | 3 | Month 3 | 2025-04-01 00:00:00.000 | 4 | Month 6 | 114 |
16 | 2025-02-01 00:00:00.000 | 4 | Month 4 | 2025-02-01 00:00:00.000 | 1 | Month 4 | 2792 |
17 | 2025-02-01 00:00:00.000 | 4 | Month 4 | 2025-03-01 00:00:00.000 | 2 | Month 5 | 391 |
18 | 2025-02-01 00:00:00.000 | 4 | Month 4 | 2025-04-01 00:00:00.000 | 3 | Month 6 | 52 |
19 | 2025-03-01 00:00:00.000 | 5 | Month 5 | 2025-03-01 00:00:00.000 | 1 | Month 5 | 1621 |
20 | 2025-03-01 00:00:00.000 | 5 | Month 5 | 2025-04-01 00:00:00.000 | 2 | Month 6 | 377 |
i_danSwellChain: Cohort Retention
Updated 2 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 cohorts AS (
SELECT
from_address
, MIN(DATE_TRUNC('month', block_timestamp)) AS cohort_month
FROM swell.core.fact_transactions
WHERE from_address != '0xdeaddeaddeaddeaddeaddeaddeaddeaddead0001'
AND from_address != '0x339d413ccefd986b1b3647a9cfa9cbbe70a30749'
GROUP BY 1
),
monthly_activity AS (
SELECT
from_address
, DATE_TRUNC('month', block_timestamp) AS activity_month
FROM swell.core.fact_transactions
),
cohort_retention AS (
SELECT
c.cohort_month
, m.activity_month
, COUNT(DISTINCT c.from_address) AS retained_users
FROM cohorts c
JOIN monthly_activity m
ON c.from_address = m.from_address
GROUP BY 1, 2
)
SELECT
cohort_month
, RANK() OVER (PARTITION BY activity_month ORDER BY cohort_month ASC) AS CohortMonth
, 'Month '||CohortMonth AS "Cohort Month"
, activity_month
, RANK() OVER (PARTITION BY cohort_month ORDER BY activity_month ASC) AS ActivityMonth
, 'Month '||(ActivityMonth + CohortMonth - 1) AS "Activity Month"
, retained_users
FROM cohort_retention
ORDER BY 1, 4
Last run: 2 days ago
21
2KB
2s