WEEK | INTENT_USERS | NEW_USERS | RETURNING_USERS | |
|---|---|---|---|---|
1 | 2025-03-10 00:00:00.000 | 198 | 66 | 160 |
2 | 2025-03-03 00:00:00.000 | 256 | 117 | 193 |
3 | 2025-02-24 00:00:00.000 | 244 | 109 | 189 |
4 | 2025-02-17 00:00:00.000 | 234 | 102 | 166 |
5 | 2025-02-10 00:00:00.000 | 263 | 143 | 185 |
6 | 2025-02-03 00:00:00.000 | 239 | 142 | 161 |
7 | 2025-01-27 00:00:00.000 | 186 | 82 | 140 |
8 | 2025-01-20 00:00:00.000 | 255 | 142 | 179 |
9 | 2025-01-13 00:00:00.000 | 263 | 160 | 168 |
10 | 2025-01-06 00:00:00.000 | 150 | 69 | 100 |
11 | 2024-12-30 00:00:00.000 | 172 | 103 | 106 |
12 | 2024-12-23 00:00:00.000 | 194 | 135 | 121 |
13 | 2024-12-16 00:00:00.000 | 226 | 169 | 118 |
14 | 2024-12-09 00:00:00.000 | 167 | 125 | 82 |
15 | 2024-12-02 00:00:00.000 | 99 | 68 | 53 |
16 | 2024-11-25 00:00:00.000 | 61 | 41 | 35 |
17 | 2024-11-18 00:00:00.000 | 69 | 53 | 35 |
18 | 2024-11-11 00:00:00.000 | 43 | 32 | 24 |
19 | 2024-11-04 00:00:00.000 | 30 | 30 | 16 |
m3jiRetention Analysis: Returning vs. New Owners
Updated 8 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
›
⌄
WITH first_seen AS (
SELECT
DISTINCT OWNER_ID,
MIN(BLOCK_TIMESTAMP) AS first_intent_date
FROM
near.defi.fact_intents
GROUP BY
OWNER_ID
)
SELECT
DATE_TRUNC('week', fi.BLOCK_TIMESTAMP) AS week,
COUNT(DISTINCT fi.OWNER_ID) AS intent_users,
COUNT(DISTINCT CASE WHEN fi.BLOCK_TIMESTAMP = fs.first_intent_date THEN fi.OWNER_ID ELSE NULL END) AS new_users,
COUNT(DISTINCT CASE WHEN fi.BLOCK_TIMESTAMP > fs.first_intent_date THEN fi.OWNER_ID ELSE NULL END) AS returning_users
FROM
near.defi.fact_intents fi
JOIN
first_seen fs ON fi.OWNER_ID = fs.OWNER_ID
GROUP BY
week
ORDER BY
week DESC;
Last run: about 8 hours agoAuto-refreshes every 24 hours
19
742B
1s