PERIOD | TOTAL_USERS | TOTAL_NEW_USERS | TOTAL_RETURNING_USERS | TOTAL_USERS_GROWTH_PERCENT | TOTAL_NEW_USERS_GROWTH_PERCENT | TOTAL_RETURNING_USERS_GROWTH_PERCENT | |
|---|---|---|---|---|---|---|---|
1 | After Dapp Portal Launch | 11289185 | 8079134 | 3210051 | 197.6 | -17.67 | 739.39 |
2 | Before Dapp Portal Launch | 33596336 | 6651542 | 26944794 | 0 | 0 | 0 |
zyroqeastern-yellow
Updated 2025-02-24
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 (
-- Track all past user activity (to correctly determine new users)
SELECT
DATE_TRUNC('day', block_timestamp) AS activity_date,
FROM_ADDRESS AS user_address
FROM
kaia.core.fact_transactions
),
daily_users AS (
-- Only count users active from 2024-07-21 onward
SELECT
activity_date,
COUNT(DISTINCT user_address) AS daily_users
FROM
user_activity
WHERE
activity_date >= '2024-07-21'
GROUP BY
activity_date
),
first_activity AS (
-- Find the first-ever activity date for each user
SELECT
user_address,
MIN(activity_date) AS first_activity_date
FROM
user_activity
GROUP BY
user_address
),
daily_new_users AS (
-- Count only users who are new from 2024-07-21 onward
SELECT
first_activity_date AS activity_date,
COUNT(DISTINCT user_address) AS daily_new_users
FROM
Last run: about 2 months ago
2
137B
59s