zyroqusers 2
Updated 2024-12-16
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 first_time_users AS (
SELECT
CONTRACT_ADDRESS,
ORIGIN_FROM_ADDRESS,
MIN(BLOCK_TIMESTAMP) AS first_transaction_time
FROM
avalanche.core.fact_event_logs
GROUP BY
CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS
HAVING
MIN(BLOCK_TIMESTAMP) >= '2024-12-16 17:00:00.000' -- First transaction within the timeframe
),
user_activity_by_type AS (
SELECT
f.CONTRACT_ADDRESS,
f.ORIGIN_FROM_ADDRESS,
labels.LABEL_TYPE AS type
FROM
first_time_users f
INNER JOIN
avalanche.core.dim_labels labels
ON
f.CONTRACT_ADDRESS = labels.ADDRESS
),
new_user_count_by_type AS (
SELECT
type,
COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS new_user_count
FROM
user_activity_by_type
GROUP BY
type
)
SELECT
type,
new_user_count