Week | Weekly Users | Quarterly Users | WAU_QAU | WAU - QAU Rate (%) | |
|---|---|---|---|---|---|
1 | 2019-06-24 00:00:00.000 | 495 | 495 | 100 | 100% |
2 | 2019-07-01 00:00:00.000 | 3012 | 631278 | 0.4771273512 | 0.48% |
3 | 2019-07-08 00:00:00.000 | 5637 | 631278 | 0.8929504909 | 0.89% |
4 | 2019-07-15 00:00:00.000 | 10594 | 631278 | 1.678182988 | 1.68% |
5 | 2019-07-22 00:00:00.000 | 10064 | 631278 | 1.594226316 | 1.59% |
6 | 2019-07-29 00:00:00.000 | 12516 | 631278 | 1.98264473 | 1.98% |
7 | 2019-08-05 00:00:00.000 | 15924 | 631278 | 2.522501972 | 2.52% |
8 | 2019-08-12 00:00:00.000 | 11911 | 631278 | 1.886807397 | 1.89% |
9 | 2019-08-19 00:00:00.000 | 254576 | 631278 | 40.327082521 | 40.33% |
10 | 2019-08-26 00:00:00.000 | 37031 | 631278 | 5.866036833 | 5.87% |
11 | 2019-09-02 00:00:00.000 | 127586 | 631278 | 20.210747088 | 20.21% |
12 | 2019-09-09 00:00:00.000 | 89279 | 631278 | 14.142580606 | 14.14% |
13 | 2019-09-16 00:00:00.000 | 16828 | 631278 | 2.665703541 | 2.67% |
14 | 2019-09-23 00:00:00.000 | 115328 | 631278 | 18.268971832 | 18.27% |
15 | 2019-09-30 00:00:00.000 | 21266 | 631278 | 3.368721863 | 3.37% |
16 | 2019-10-07 00:00:00.000 | 56530 | 4208728 | 1.343161164 | 1.34% |
17 | 2019-10-14 00:00:00.000 | 100378 | 4208728 | 2.384996132 | 2.38% |
18 | 2019-10-21 00:00:00.000 | 123980 | 4208728 | 2.945783144 | 2.95% |
19 | 2019-10-28 00:00:00.000 | 311018 | 4208728 | 7.389833698 | 7.39% |
20 | 2019-11-04 00:00:00.000 | 574322 | 4208728 | 13.645975696 | 13.65% |
i_danKaia: Weekly Stickiness Ratio
Updated 2025-02-26
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
›
⌄
WITH user_activity AS (
SELECT
from_address
, DATE_TRUNC('week', block_timestamp) AS activity_week
, DATE_TRUNC('quarter', block_timestamp) AS activity_quarter
FROM kaia.core.fact_transactions
),
wau AS (
SELECT
activity_week
, COUNT(DISTINCT from_address) AS weekly_active_users
FROM user_activity
GROUP BY 1
),
qau AS (
SELECT
activity_quarter
, COUNT(DISTINCT from_address) AS quarterly_active_users
FROM user_activity
GROUP BY 1
)
SELECT
w.activity_week AS "Week"
, w.weekly_active_users AS "Weekly Users"
, q.quarterly_active_users AS "Quarterly Users"
, (w.weekly_active_users::float / q.quarterly_active_users) * 100 AS WAU_QAU
, ROUND(WAU_QAU, 2)||'%' AS "WAU - QAU Rate (%)"
FROM wau w
LEFT JOIN qau q
ON DATE_TRUNC('quarter', w.activity_week) = q.activity_quarter
ORDER BY 1
Last run: about 1 month ago
...
297
18KB
313s