datavortexWeekly Dapps rentention
Updated 2025-01-29
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
›
⌄
WITH first_dapp_tx AS (
SELECT
from_address,
MIN(DATE_TRUNC('week', block_timestamp)) AS first_tx_week
FROM Kaia.core.fact_transactions t
JOIN Kaia.core.dim_labels d ON t.to_address = d.address
WHERE d.label_type = 'dapp'
GROUP BY from_address
),
retention_dapp_users AS (
SELECT
COUNT(DISTINCT t.from_address) AS returning_users,
DATE_TRUNC('week', t.block_timestamp) AS week
FROM Kaia.core.fact_transactions t
JOIN first_dapp_tx f ON t.from_address = f.from_address
JOIN Kaia.core.dim_labels d ON t.to_address = d.address
WHERE t.block_timestamp >= f.first_tx_week
AND t.block_timestamp >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY week
),
cohort_size AS (
SELECT COUNT(DISTINCT from_address) AS cohort_size
FROM first_dapp_tx
)
SELECT
r.week AS "Week",
c.cohort_size AS "Cohort Size",
r.returning_users AS "Returning Users",
ROUND((r.returning_users * 100.0) / c.cohort_size, 2) AS "Weekly Retention Rate (%)"
FROM retention_dapp_users r
JOIN cohort_size c
ORDER BY r.week;
QueryRunArchived: QueryRun has been archived