with tc_swaps as ( select distinct first_value(lower(from_address)) over (partition by tx_id order by block_timestamp) as from_address, last_value(lower(native_to_address)) over (partition by tx_id order by block_timestamp) as to_address, case when to_address = from_address and rlike(from_address,'0x[a-z0-9]{40}') then from_address when to_address <> from_address and rlike(from_address,'0x[a-z0-9]{40}') then from_address when to_address <> from_address and rlike(to_address,'0x[a-z0-9]{40}') then to_address end as swapper, -- ETH swapper 'THORChain' as swap_program, block_timestamp, tx_id as tx_hash, first_value(from_asset) over (partition by tx_id order by block_timestamp, min_to_amount) as symbol_in, first_value(from_amount) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_in, first_value(from_amount_usd) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_in_usd, last_value(to_asset) over (partition by tx_id order by block_timestamp, min_to_amount) as symbol_out, last_value(to_amount) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_out, last_value(to_amount_usd) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_out_usd from flipside_prod_db.thorchain.swaps where datediff('month',block_timestamp,current_date()) <= 3 and blockchain = 'ETH' qualify (rlike(lower(from_address),'0x[a-z0-9]{40}') or rlike(lower(to_address),'0x[a-z0-9]{40}')) ), eth_swaps as ( select block_timestamp, tx_hash, origin_from_address as swapper, platform as swap_program, ifnull(symbol_in, token_in) as symbol_in, amount_in, amount_in_usd, ifnull(symbol_out, token_out) as symbol_out, amount_out, amount_out_usd from ethereum.core.ez_dex_swaps swaps where datediff('month',block_timestamp,current_date()) <= 3 and exists (select 1 from tc_swaps where lower(tc_swaps.from_address) = swaps.origin_from_address or lower(tc_swaps.to_address) = swaps.origin_from_address ) ), all_swaps as ( select block_timestamp, tx_hash, swapper, swap_program, regexp_replace(symbol_in,'^[A-z]*.|-[A-z0-9]*$') as symbol_in, amount_in, amount_in_usd, regexp_replace(symbol_out,'^[A-z]*.|-[A-z0-9]*$') as symbol_out, amount_out, amount_out_usd from tc_swaps where exists (select 1 from eth_swaps where eth_swaps.swapper = tc_swaps.swapper) union all select * from eth_swaps ), arbs as ( select swapper, block_timestamp, tx_hash, swap_program, symbol_in, amount_in, amount_in_usd, symbol_out, amount_out, amount_out_usd, lead(block_timestamp,1) over (partition by swapper order by block_timestamp) as next_timestamp, lead(tx_hash,1) over (partition by swapper order by block_timestamp) as next_tx, lead(swap_program,1) over (partition by swapper order by block_timestamp) as next_program, lead(symbol_in,1) over (partition by swapper order by block_timestamp) as next_symbol_in, lead(amount_in,1) over (partition by swapper order by block_timestamp) as next_amount_in, lead(amount_in_usd,1) over (partition by swapper order by block_timestamp) as next_amount_in_usd, lead(symbol_out,1) over (partition by swapper order by block_timestamp) as next_symbol_out, lead(amount_out,1) over (partition by swapper order by block_timestamp) as next_amount_out, lead(amount_out_usd,1) over (partition by swapper order by block_timestamp) as next_amount_out_usd from all_swaps qualify swap_program <> next_program and (rlike(symbol_out,concat('.?',next_symbol_in,'$')) or rlike(next_symbol_in,concat('.?',symbol_out,'$'))) -- and (rlike(next_symbol_out,concat('.?',symbol_in,'$')) -- or rlike(symbol_in,concat('.?',next_symbol_out,'$'))) -- and round(amount_out,2) = round(next_amount_in,2) and case when symbol_out in ('ETH','WETH') then round(amount_out,2) = round(next_amount_in,2) when symbol_out in ('BTC','WBTC') then round(amount_out,3) = round(next_amount_in,3) else floor(amount_out) = floor(next_amount_in) end ), aggregated as ( select swapper, sum(amount_in_usd) as arb_amount from arbs where amount_in_usd is not null group by 1 ), binned as ( select case when arb_amount < 10 then (floor(arb_amount))::string when arb_amount < 100 then (floor(arb_amount/10.0) * 10)::string when arb_amount < 1000 then (floor(arb_amount/100.0) * 100)::string when arb_amount < 10000 then (floor(arb_amount/1000.0) * 1000)::string when arb_amount < 100000 then (floor(arb_amount/10000.0) * 10000)::string when arb_amount < 1000000 then (floor(arb_amount/100000.0) * 100000)::string else arb_amount::string end as bine, count(*) as arbers from aggregated group by 1 ) select * from binned

Ethereum THORChain Arbitrage