Arkanwallet 1
Updated 2023-01-01
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
table1 AS (
SELECT
tx_signer 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
near.core.fact_transactions
where block_timestamp >= '2022-01-01'
group by 1
),
table2 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
table1
WHERE tx_counts < 10000
GROUP BY 1
)
SELECT
first_tx,
new_users as "New Wallets",
Run a query to Download Data