ELAYDaily number of new users (on Solana)
Updated 2022-11-27Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
›
⌄
with DefiTable as ( select address,label
from solana.core.dim_labels
where label_type in ('defi','dex')
union all
select 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' as address, 'OpenBook' as label)
select mindate::date as day,
case when mindate >= '2022-11-08' then 'After FTX Collapse'
else 'Before FTX Collapse' end as timespan, project_name,
count (distinct user1) as New_Users, sum (new_users) over (partition by project_name order by day) as Total_Users
from (
select instruction:accounts[1] as user1,
initcap(label) as Project_Name, min (block_timestamp) as mindate
from solana.core.fact_events t1 join DefiTable t2 on t1.program_id = t2.address
where succeeded = 'TRUE'
group by 1,2)
where mindate >= CURRENT_DATE - 21
group by 1,2,3
Run a query to Download Data