0xHaM-dCumulative Ethereum New Wallets by Days Active
Updated 2023-01-19Copy Reference Fork
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 wallet_age AS (
SELECT
FROM_ADDRESS as address,
count(distinct tx_hash) as tx_counts,
count(distinct date(block_timestamp)) as days_active,
min(date(block_timestamp)) as first_tx,
max(date(block_timestamp)) as last_tx,
datediff('day', last_tx, getdate()) as days_last_active,
datediff('day', first_tx, getdate()) as age_today
from ethereum.core.fact_transactions
group by 1
),
new_users_stat AS (
SELECT
first_tx,
count(distinct address) as new_users,
count(distinct case when days_active >= 7 AND days_last_active <= 7 then address else null end) as active_user,
count(distinct case when days_active = 1 then address else null end) as one_day_user,
count(distinct case when days_active >= 2 then address else null end) as two_or_more_days_user,
count(distinct case when days_active >= 5 then address else null end) as five_or_more_days_user,
count(distinct case when days_active >= 7 then address else null end) as seven_or_more_days_user,
count(distinct case when days_active >= 10 then address else null end) as fourteen_or_more_days_user,
count(distinct case when days_active >= 50 then address else null end) as fifty_or_more_days_user,
count(distinct case when days_active >= 100 then address else null end) as hundred_or_more_days_user,
count(distinct case when days_active >= 200 then address else null end) as two_hundred_or_more_days_user
FROM wallet_age
WHERE tx_counts < 10000
GROUP BY 1
)
SELECT
first_tx,
new_users as "New Wallets",
active_user as "New Active Users",
sum(new_users) over (order by first_tx) as "Cumulative New Wallets",
sum(active_user) over (order by first_tx) as "Cumulative Active Users",
sum(one_day_user) over (order by first_tx) as "Active Only 1 Day",
Run a query to Download Data