Sardius-0626Arbitrum New and Active Users
Updated 2024-03-17
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
›
⌄
-- Calculates new wallets by the first time they sent a transaction
WITH t1 AS (
SELECT
from_address,
MIN(block_timestamp) AS first_tx_timestamp
FROM arbitrum.core.fact_transactions
WHERE origin_function_signature <> '0x6bf6a42d'
GROUP BY from_address
HAVING first_tx_timestamp::DATE >= '2023-01-01'
AND first_tx_timestamp::DATE < '2024-03-01'
)
SELECT
DATE_TRUNC('month', first_tx_timestamp) AS month,
'new' AS type,
COUNT(from_address) AS addresses
FROM t1
GROUP BY month
UNION
SELECT
DATE_TRUNC('month', block_timestamp) AS month,
'total active' AS type,
COUNT(DISTINCT from_address) AS addresses
FROM arbitrum.core.fact_transactions
WHERE block_timestamp::DATE >= '2023-01-01'
AND block_timestamp::DATE < '2024-03-01'
GROUP BY month
QueryRunArchived: QueryRun has been archived