nitsOld vs New Wallet share
Updated 2022-07-08
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
›
⌄
with new_users as ( select min(block_timestamp) as f_u , a.value as new_wallet
from solana.core.fact_transactions , lateral flatten(input => signers) a
group by 2),
new_user as ( select DISTINCT new_wallet
from new_users
where date(f_u) >= CURRENT_DATE - 90),
new_stake as ( select date(block_timestamp) as date , a.value as new_wallet
from solana.core.fact_transactions , lateral flatten(input => signers) a
where instructions[0]:programId = 'Stake11111111111111111111111111111111111111' and a.value in ( select new_wallet from new_user)
and block_timestamp::date >= CURRENT_DATE - 90)
,
old_stake as ( select date(block_timestamp) as date , a.value as new_wallet
from solana.core.fact_transactions , lateral flatten(input => signers) a
where instructions[0]:programId = 'Stake11111111111111111111111111111111111111' and a.value not in ( select new_wallet from new_user)
and block_timestamp::date >= CURRENT_DATE - 90)
select 'New Wallet' as type , date , count(DISTINCT(new_wallet)) as total , sum(total) over (order by date asc) as cum_total
from new_stake
group by 1,2
UNION
select 'Old Wallet' as type , date , count(DISTINCT(new_wallet)) as total, sum(total) over (order by date asc) as cum_total
from old_stake
group by 1,2
Run a query to Download Data