pavlyshynpivot table sol/usdc arca-raydium
    Updated 2022-08-23
    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