MLDZMNzd8
Updated 2023-03-31
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 t1 as (select
case
when PROGRAM_ID in ('ZETAxsqBRek56DhiGXrn75yj2NHU3aYUnxvHXpkf3aD') then 'Zeta'
when PROGRAM_ID in ('dammHkt7jmytvbS3nHTxQNEcP59aE57nxwV21YdqEDNm','dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH') then 'Drift'
end as programs,
BLOCK_TIMESTAMP,
tx_id,
signers[0] as wl
from solana.core.fact_events
where SUCCEEDED='TRUE'
and BLOCK_TIMESTAMP>='2023-01-01'
having programs is not null
),
t2 as (select
programs,
s.BLOCK_TIMESTAMP,
s.SIGNERS[0] as wl1,
s. tx_id,
ROW_NUMBER() OVER (partition by s.SIGNERS[0] order by s.BLOCK_TIMESTAMP) as t_n
from solana.core.fact_transactions s left join t1 a on s.tx_id=a.tx_id
where s.SUCCEEDED='TRUE'
and s.SIGNERS[0] in (select wl from t1)
and s.BLOCK_TIMESTAMP>='2023-01-01'
)
select
t1.programs,
case
when t1.tx_id=t2.tx_id then 'Create to interact with program'
else 'Old user' end as gp,
count(distinct wl1) as no_users
from t2 left join t1 on wl1=wl
where t_n=1
group by 1,2
Run a query to Download Data