NEW_USERS | ACTIVE_USERS | TOTAL_USERS | |
---|---|---|---|
1 | 198184 | 198184 | 3346485 |
crypto_gostUsers
Updated 2024-12-12
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 recent_transactions AS (
SELECT
from_address AS address,
MIN(block_timestamp) OVER (PARTITION BY from_address) AS first_tx_date,
block_timestamp
FROM BLAST.core.fact_transactions
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
UNION ALL
SELECT
to_address AS address,
MIN(block_timestamp) OVER (PARTITION BY to_address) AS first_tx_date,
block_timestamp
FROM BLAST.core.fact_transactions
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
),
user_metrics AS (
SELECT
COUNT(DISTINCT CASE WHEN first_tx_date >= DATEADD(day, -30, CURRENT_DATE()) THEN address END) AS new_users,
COUNT(DISTINCT address) AS active_users
FROM recent_transactions
),
total_users AS (
SELECT
COUNT(DISTINCT from_address) + COUNT(DISTINCT to_address) AS total_users
FROM BLAST.core.fact_transactions
)
-- Final aggregation
SELECT
u.new_users,
u.active_users,
t.total_users
Last run: 2 months ago
1
25B
11s