NEW_ADDRESSES_24H | NEW_ADDRESSES_7D | NEW_ADDRESSES_30D | TOTAL_NEW_ADDRESSES | PERCENTAGE_NEW_24H | PERCENTAGE_NEW_7D | PERCENTAGE_NEW_30D | |
|---|---|---|---|---|---|---|---|
1 | 57272 | 385602 | 2686022 | 68778515 | 0.08327 | 0.560643 | 3.905321 |
Specterduration new users
Updated 2025-05-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 address_first_tx AS (
-- Get the first transaction date for each address
SELECT
sender,
MIN(block_timestamp) AS first_tx
FROM
aptos.core.fact_transactions
WHERE
tx_type = 'user_transaction'
AND success = TRUE
GROUP BY
sender
)
SELECT
COUNT(DISTINCT CASE
WHEN first_tx >= CURRENT_TIMESTAMP - INTERVAL '24 hours' THEN sender
END) AS new_addresses_24h,
COUNT(DISTINCT CASE
WHEN first_tx >= CURRENT_TIMESTAMP - INTERVAL '7 days' THEN sender
END) AS new_addresses_7d,
COUNT(DISTINCT CASE
WHEN first_tx >= CURRENT_TIMESTAMP - INTERVAL '30 days' THEN sender
END) AS new_addresses_30d,
COUNT(DISTINCT sender) AS total_new_addresses,
-- Calculate the percentage of new addresses in the last 24 hours compared to total
(new_addresses_24h * 100.0 / total_new_addresses) AS percentage_new_24h,
(new_addresses_7d * 100.0 / total_new_addresses) AS percentage_new_7d,
-- Calculate the percentage of new addresses in the last 30 days compared to total
(new_addresses_30d * 100.0 / total_new_addresses) AS percentage_new_30d
FROM
address_first_tx
WHERE first_tx >= '2024-01-01';
Last run: 29 days ago
1
59B
211s