developer_violaKaia Retention
Updated 2025-03-21Copy Reference Fork
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
›
⌄
WITH users AS (
SELECT
from_address AS user,
MIN(DATE_TRUNC('month', block_timestamp)) AS month_cohort,
MIN(DATE_TRUNC('day', block_timestamp)) AS first_month,
COUNT(DISTINCT block_timestamp) AS distinct_days_active,
MAX(DATE_TRUNC('day', block_timestamp)) AS last_month_active,
COUNT(DISTINCT to_address) AS distinct_interactions
FROM kaia.core.fact_transactions
WHERE tx_succeeded = TRUE
GROUP BY 1
HAVING MIN(DATE_TRUNC('month', block_timestamp)) >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 month')
)
SELECT
month_cohort,
COUNT(DISTINCT user) AS total_users,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '1 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_1,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '2 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_2,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '3 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_3,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '4 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_4,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '5 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_5,
ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '6 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_6
FROM users
WHERE month_cohort >= DATE('2024-08-29')
GROUP BY month_cohort
ORDER BY month_cohort;
QueryRunArchived: QueryRun has been archived