WEEK | RETAINED_USERS | TOTAL_USERS | RETENTION_RATE | |
|---|---|---|---|---|
1 | 2025-02-24 00:00:00.000 | 8 | 244 | 3.28 |
2 | 2025-02-17 00:00:00.000 | 75 | 234 | 32.05 |
3 | 2025-02-10 00:00:00.000 | 67 | 263 | 25.48 |
4 | 2025-02-03 00:00:00.000 | 65 | 239 | 27.2 |
5 | 2025-01-27 00:00:00.000 | 50 | 186 | 26.88 |
6 | 2025-01-20 00:00:00.000 | 59 | 255 | 23.14 |
7 | 2025-01-13 00:00:00.000 | 72 | 263 | 27.38 |
8 | 2025-01-06 00:00:00.000 | 48 | 150 | 32 |
9 | 2024-12-30 00:00:00.000 | 41 | 172 | 23.84 |
10 | 2024-12-23 00:00:00.000 | 34 | 194 | 17.53 |
11 | 2024-12-16 00:00:00.000 | 38 | 226 | 16.81 |
12 | 2024-12-09 00:00:00.000 | 38 | 167 | 22.75 |
13 | 2024-12-02 00:00:00.000 | 26 | 99 | 26.26 |
14 | 2024-11-25 00:00:00.000 | 20 | 61 | 32.79 |
15 | 2024-11-18 00:00:00.000 | 15 | 69 | 21.74 |
16 | 2024-11-11 00:00:00.000 | 13 | 43 | 30.23 |
17 | 2024-11-04 00:00:00.000 | 11 | 30 | 36.67 |
adriaparcerisasneari retained users
Updated 2025-03-02
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH user_activity AS (
SELECT
owner_id,
DATE_TRUNC('week', block_timestamp) as week,
COUNT(DISTINCT tx_hash) as weekly_actions
FROM near.defi.fact_intents
GROUP BY 1, 2
),
retention_metrics AS (
SELECT
ua.week,
COUNT(DISTINCT CASE WHEN next.owner_id IS NOT NULL THEN ua.owner_id END) as retained_users,
COUNT(DISTINCT ua.owner_id) as total_users,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN next.owner_id IS NOT NULL THEN ua.owner_id END) /
NULLIF(COUNT(DISTINCT ua.owner_id), 0), 2) as retention_rate
FROM user_activity ua
LEFT JOIN user_activity next
ON ua.owner_id = next.owner_id
AND DATEDIFF('week', ua.week, next.week) = 1
GROUP BY 1
ORDER BY 1
)
SELECT * FROM retention_metrics where week<trunc(current_date,'week') order by 1 desc
Last run: 3 months ago
17
687B
1s