walemathsCurrent Week Users
Updated 2024-06-15
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
›
⌄
WITH previous_week_users AS (
SELECT
DISTINCT sender AS user_id
FROM
aptos.core.fact_transactions WHERE
block_timestamp BETWEEN DATEADD(day, -14, CURRENT_TIMESTAMP()) AND DATEADD(day, -7, CURRENT_TIMESTAMP())
),
current_week_users AS (
SELECT
DISTINCT sender AS user_id
FROM
aptos.core.fact_transactions
WHERE
block_timestamp BETWEEN DATEADD(day, -7, CURRENT_TIMESTAMP()) AND CURRENT_TIMESTAMP()
)
SELECT
COUNT(pw.user_id) AS previous_week_users,
COUNT(cw.user_id) AS current_week_users,
COUNT(DISTINCT pw.user_id) AS unique_previous_week_users,
COUNT(DISTINCT cw.user_id) AS unique_current_week_users,
COUNT(DISTINCT pw.user_id) * 100.0 / COUNT(DISTINCT cw.user_id) AS retention_rate
FROM
previous_week_users pw
LEFT JOIN current_week_users cw ON pw.user_id = cw.user_id;
QueryRunArchived: QueryRun has been archived