i_danKaia: New Vs Returning Users
Updated 2025-02-26Copy 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
›
⌄
WITH first_tx AS (
SELECT
from_address
, MIN(block_timestamp) AS first_quarter
FROM kaia.core.fact_transactions
GROUP BY 1
),
user_activity AS (
SELECT
t.from_address
, DATE_TRUNC('quarter', t.block_timestamp) AS activity_quarter
, DATE_TRUNC('quarter', f.first_quarter) AS first_tx_quarter
FROM kaia.core.fact_transactions t
JOIN first_tx f ON t.from_address = f.from_address
)
SELECT
activity_quarter AS "Quarter"
, COUNT(DISTINCT CASE WHEN activity_quarter = first_tx_quarter THEN from_address END) AS "New Users"
, COUNT(DISTINCT CASE WHEN activity_quarter > first_tx_quarter THEN from_address END) AS "Returning Users"
, COUNT(DISTINCT from_address) AS "Quarterly Users" --
FROM user_activity
GROUP BY 1
ORDER BY 1
QueryRunArchived: QueryRun has been archived