ACTIVITY_PERIOD | USER_CATEGORY | USER_COUNT | |
---|---|---|---|
1 | 2025-02-09 00:00:00.000 | Repeat Users | 150 |
2 | 2025-02-09 00:00:00.000 | First-Time Users | 12 |
3 | 2025-02-08 00:00:00.000 | First-Time Users | 37 |
4 | 2025-02-08 00:00:00.000 | Repeat Users | 292 |
5 | 2025-02-07 00:00:00.000 | Repeat Users | 360 |
6 | 2025-02-07 00:00:00.000 | First-Time Users | 34 |
7 | 2025-02-06 00:00:00.000 | First-Time Users | 39 |
8 | 2025-02-06 00:00:00.000 | Repeat Users | 389 |
9 | 2025-02-05 00:00:00.000 | First-Time Users | 42 |
10 | 2025-02-05 00:00:00.000 | Repeat Users | 375 |
11 | 2025-02-04 00:00:00.000 | Repeat Users | 436 |
12 | 2025-02-04 00:00:00.000 | First-Time Users | 77 |
13 | 2025-02-03 00:00:00.000 | Repeat Users | 567 |
14 | 2025-02-03 00:00:00.000 | First-Time Users | 126 |
15 | 2025-02-02 00:00:00.000 | Repeat Users | 446 |
16 | 2025-02-02 00:00:00.000 | First-Time Users | 88 |
17 | 2025-02-01 00:00:00.000 | Repeat Users | 424 |
18 | 2025-02-01 00:00:00.000 | First-Time Users | 76 |
19 | 2025-01-31 00:00:00.000 | First-Time Users | 54 |
20 | 2025-01-31 00:00:00.000 | Repeat Users | 407 |
gigiokobavertical-indigo
Updated 2025-02-09
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
SELECT
DATE_TRUNC('day', log_data.event_time) AS activity_period,
CASE
WHEN log_data.rank = 1 THEN 'First-Time Users'
ELSE 'Repeat Users'
END AS user_category,
COUNT(DISTINCT log_data.user_address) AS user_count
FROM (
SELECT
origin_from_address AS user_address,
block_timestamp AS event_time,
ROW_NUMBER() OVER (PARTITION BY origin_from_address ORDER BY block_timestamp ASC) AS rank
FROM avalanche.core.ez_decoded_event_logs
WHERE contract_address = '0xaaab9d12a30504559b0c5a9a5977fee4a6081c6b'
) AS log_data
GROUP BY activity_period, user_category
ORDER BY activity_period DESC;
Last run: 14 days ago
...
859
40KB
8s