iboo-jbj2MVThe number of transactions that Gain has earned through the arbitrage range
    Updated 2022-03-31
    with from_usdc_or_usdt as (
    select tx_group_id , swapper ,swap_from_amount,swap_to_amount,swap_to_asset_id , block_timestamp
    from algorand.swaps
    where swap_from_amount >0
    and swap_from_asset_id in (31566704 ,312769)
    and block_timestamp>= '2022-01-01'
    ),
    to_usdc_or_usdt as (
    select s.tx_group_id as tx, s.swapper as swapper , f.swap_from_amount as stable_from_amount ,
    s.swap_to_amount as stable_to_amount
    from algorand.swaps s join from_usdc_or_usdt f
    on s.swapper = f.swapper
    and s.swap_from_asset_id = f.swap_to_asset_id
    and f.swap_from_amount <=s.swap_to_amount
    and f.swap_to_amount = s.swap_from_amount
    and date_trunc('second',f.block_timestamp) <date_trunc('second',s.block_timestamp)
    where s.swap_from_amount >0
    and s.swap_to_asset_id in (31566704 ,312769)
    and s.block_timestamp>= '2022-01-01'
    )
    select
    case when ((stable_to_amount - stable_from_amount)/stable_from_amount)*100 BETWEEN 0 and 1 then '0-1 %'
    when ((stable_to_amount - stable_from_amount)/stable_from_amount)*100 BETWEEN 1 and 2 THEN '1-3 %'
    when ((stable_to_amount - stable_from_amount)/stable_from_amount)*100 BETWEEN 2 and 5 THEN '3-6 %'
    when ((stable_to_amount - stable_from_amount)/stable_from_amount)*100 BETWEEN 5 and 10 THEN '5-10 %'
    when ((stable_to_amount - stable_from_amount)/stable_from_amount)*100 BETWEEN 10 and 50 THEN '10-50 %'
    else '> 50%'
    end as Arbitrage_range,
    count (DISTINCT swapper) as wallets,
    count (DISTINCT tx) as transactions
    from to_usdc_or_usdt
    group by 1