pavlyshynpivot table sol/usdc arca-raydium
Updated 2022-08-23
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
›
⌄
select
timestamp,
orca_price_f,
raydium_price_f,
diff,
coms_threshold
from(
select
timestamp,
orca_price,
coalesce(orca_price, last_value(orca_price ignore nulls) over (order by timestamp rows between unbounded preceding and current row)) as orca_price_f,
raydium_price,
coalesce(raydium_price, last_value(raydium_price ignore nulls) over (order by timestamp rows between unbounded preceding and current row)) as raydium_price_f,
ABS(orca_price_f/raydium_price_f-1) as diff,
0.0055 as coms_threshold
from(
select *
from ( select block_timestamp, swap_program,
iff(swap_from_mint='So11111111111111111111111111111111111111112', div0(swap_to_amount,swap_from_amount),div0(swap_from_amount,swap_to_amount)) as price
from solana.fact_swaps
where succeeded = 'TRUE'
and swap_from_amount>0.0001 and swap_to_amount>0.0001
and tx_id in ( select tx_id
from solana.fact_events
where program_id in ('675kPX9MHTjS2zt1qfr1NYHuzeLXfQM9H24wFSUt1Mp8',
'DjVE6JNiYqPL2QXyCUUh8rNjHrbz9hXHNYt99MQ59qw1',
'9W959DqEETiGZocYWCQPaJ6sBmUzgfxXfqGeTEdp3aQP') --orca, rayd program_ids
and tx_id not in (select distinct(tx_id) from solana.fact_events
where event_type = 'transferChecked'
and BLOCK_TIMESTAMP>CURRENT_DATE-INTERVAL '7 DAY')
and event_type is null
and BLOCK_TIMESTAMP>CURRENT_DATE-INTERVAL '7 DAY'
group by tx_id
having count(*) = 1 )
and swap_program in ('orca' ,'raydium v4')
and swap_from_mint in ('So11111111111111111111111111111111111111112',
Run a query to Download Data