ELAY Number of Transactions on Serum Before & After Collapse(Daily)
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
19
20
21
22
23
24
25
26
27
›
⌄
with newtable as (
select mindate::date as day,
count (distinct user1) as New_Users,
sum (new_users) over (order by day) as Total_Users
from (
select instruction:accounts[1] as user1,
min (block_timestamp) as mindate
from solana.core.fact_events
where program_id in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o','4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')
and succeeded = 'TRUE'
group by 1)
group by 1)
select block_timestamp::date as date,
case when date >= '2022-11-08' then 'After FTX Collapse'
else 'Before FTX Collapse' end as timespan,
new_users,
total_users,
count (distinct tx_id) as TX_Count,
count (distinct instruction:accounts[1]) as Users_Count
from solana.core.fact_events t1 join newtable t2 on t1.block_timestamp::date = t2.day
where program_id in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o','4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')
and succeeded = 'TRUE'
and block_timestamp >= CURRENT_DATE - 21
group by 1,2,3,4
Run a query to Download Data