check_skedArbitrum Historical Unique Addresses
Updated 2023-04-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
›
⌄
WITH all_addresses AS (
SELECT sender_address AS address, _day
FROM (
SELECT FROM_ADDRESS AS sender_address, BLOCK_TIMESTAMP AS _day
FROM arbitrum.core.fact_token_transfers
UNION ALL
SELECT ETH_FROM_ADDRESS AS sender_address, BLOCK_TIMESTAMP AS _day
FROM arbitrum.core.ez_eth_transfers
)
WHERE sender_address IS NOT NULL
UNION
SELECT receiver_address AS address, _day
FROM (
SELECT TO_ADDRESS AS receiver_address, BLOCK_TIMESTAMP AS _day
FROM arbitrum.core.fact_token_transfers
UNION ALL
SELECT ETH_TO_ADDRESS AS receiver_address, BLOCK_TIMESTAMP AS _day
FROM arbitrum.core.ez_eth_transfers
)
WHERE receiver_address IS NOT NULL
),
distinct_addresses AS (
SELECT DISTINCT address, _day
FROM all_addresses
)
SELECT DATE_TRUNC('day', _day) AS _day, COUNT(DISTINCT address) AS "Unique Addresses_ARB"
FROM distinct_addresses
GROUP BY DATE_TRUNC('day', _day)
ORDER BY DATE_TRUNC('day', _day)