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',