SIGNUP_DATE | DAY_1_RETENTION | DAY_2_RETENTION | DAY_3_RETENTION | DAY_7_RETENTION | DAY_14_RETENTION | DAY_30_RETENTION | |
|---|---|---|---|---|---|---|---|
1 | 2025-01-07 00:00:00.000 | 6 | 5 | 3 | 3 | 2 | 1 |
2 | 2025-01-08 00:00:00.000 | 7 | 7 | 1 | 7 | 3 | 1 |
3 | 2025-01-09 00:00:00.000 | 13 | 10 | 10 | 14 | 3 | 0 |
4 | 2025-01-10 00:00:00.000 | 0 | 0 | 2 | 3 | 1 | 0 |
5 | 2025-01-11 00:00:00.000 | 1 | 2 | 3 | 1 | 1 | 2 |
6 | 2025-01-12 00:00:00.000 | 2 | 0 | 1 | 0 | 0 | 0 |
7 | 2025-01-13 00:00:00.000 | 12 | 5 | 11 | 2 | 5 | 6 |
8 | 2025-01-14 00:00:00.000 | 6 | 1 | 3 | 2 | 3 | 3 |
9 | 2025-01-15 00:00:00.000 | 11 | 7 | 2 | 6 | 3 | 8 |
10 | 2025-01-16 00:00:00.000 | 10 | 8 | 8 | 6 | 6 | 5 |
11 | 2025-01-17 00:00:00.000 | 0 | 0 | 4 | 6 | 2 | 4 |
12 | 2025-01-18 00:00:00.000 | 1 | 0 | 0 | 0 | 0 | 1 |
13 | 2025-01-19 00:00:00.000 | 1 | 1 | 1 | 1 | 0 | 2 |
14 | 2025-01-20 00:00:00.000 | 3 | 4 | 6 | 1 | 2 | 9 |
15 | 2025-01-21 00:00:00.000 | 7 | 9 | 9 | 4 | 2 | 7 |
16 | 2025-01-22 00:00:00.000 | 14 | 9 | 4 | 6 | 7 | 7 |
17 | 2025-01-23 00:00:00.000 | 17 | 4 | 3 | 10 | 9 | 6 |
18 | 2025-01-24 00:00:00.000 | 6 | 4 | 10 | 1 | 1706 | 5 |
19 | 2025-01-25 00:00:00.000 | 2 | 1 | 1 | 0 | 0 | 2 |
20 | 2025-01-26 00:00:00.000 | 4 | 2 | 1 | 2 | 1 | 7 |
oxkimiacorporate-amaranth
Updated 2025-03-07
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
›
⌄
-- Retention for Monad Testnet
WITH user_first_transaction AS (
SELECT
From_address,
MIN(DATE(block_timestamp)) AS first_transaction_date
FROM
monad.testnet.fact_transactions
GROUP BY
From_address
),
user_transactions AS (
SELECT
t.From_address,
DATE(t.block_timestamp) AS transaction_date,
uft.first_transaction_date
FROM
monad.testnet.fact_transactions t
JOIN
user_first_transaction uft
ON
t.From_address = uft.From_address
)
SELECT
uft.first_transaction_date AS signup_date,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 1, uft.first_transaction_date) THEN From_address END) AS day_1_retention,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 2, uft.first_transaction_date) THEN From_address END) AS day_2_retention,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 3, uft.first_transaction_date) THEN From_address END) AS day_3_retention,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 7, uft.first_transaction_date) THEN From_address END) AS day_7_retention,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 14, uft.first_transaction_date) THEN From_address END) AS day_14_retention,
COUNT(DISTINCT CASE WHEN transaction_date = DATEADD(day, 30, uft.first_transaction_date) THEN From_address END) AS day_30_retention
FROM
user_transactions uft
GROUP BY
uft.first_transaction_date
Last run: about 2 months ago
60
3KB
21s