shreexSerum as
Updated 2022-11-22
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 serum as (
select
distinct address as address,
address_name as program
from solana.core.dim_labels
where label_type in ('defi','dex') and address_name ilike '%serum%'),
new_Users as (
select
INSTRUCTION:accounts[1] as users,
min(block_timestamp) as min_date
from solana.core.fact_events inner join serum on program_id = address
where block_timestamp >= '2022-11-01' and program_id=address
GROUP BY users
),
counts_users as (
select
date_trunc('day',min_date) as datee,
count(distinct users) as new_users,
sum(new_users) over (order by datee ) as cumulative_users
from new_users
GROUP BY datee
), dates as (
select
date_trunc('day',block_timestamp) as date,
count(DISTINCT INSTRUCTION:accounts[1]) as unique_users,
count(distinct tx_id) as txs,
avg(txs) over (order by date) as avg_transactions
from solana.core.fact_events inner join serum on program_id = address
where date >= '2022-11-01' and program_id=address
group by date
order by date
)
select
case
when date >= '2022-11-08' then 'After FTX Collapse'
when date < '2022-11-08' then 'Before FTX Collapse'