keshanAlgo dex arbitrage aggregates
    Updated 2022-03-30
    with txs as (
    select block_timestamp as date,
    swapper,
    (case swap_from_asset_id when 31566704 then 'USDC'
    when 312769 then 'USDT' end) as swap_from_coin,
    swap_from_amount,
    swap_to_amount,
    swap_to_asset_id,
    tx_group_id,
    lead(tx_group_id) over (partition by swapper order by date) as next_swap_tx,
    lead(block_timestamp) over (partition by swapper order by date) as next_swap_date,
    lead(swap_from_amount) over (partition by swapper order by date) as next_swap_from_amount,
    lead(swap_from_asset_id) over (partition by swapper order by date) as next_swap_from_asset_id,
    lead(swap_to_amount) over (partition by swapper order by date) as next_swap_to_amount,
    lead(swap_to_asset_id) over (partition by swapper order by date) as next_swap_to_asset_id,
    (case next_swap_to_asset_id when 31566704 then 'USDC'
    when 312769 then 'USDT' else null end) as swap_to_coin
    from algorand.swaps
    where date >= '2022-1-1'
    and (swap_from_asset_id = 31566704 or swap_from_asset_id = 312769)
    ),
    accumilations as (select *, datediff(second, date, next_swap_date) as time_delta_in_seconds, (next_swap_to_amount - swap_from_amount) as profit
    from (select * from txs)
    where next_swap_tx is not null
    and next_swap_from_asset_id = swap_to_asset_id
    and swap_to_amount = next_swap_from_amount
    and (next_swap_to_asset_id = 31566704 or next_swap_to_asset_id = 312769)
    )

    select date::date as date, avg(profit) avg_profit, avg(time_delta_in_seconds) as avg_time_delta_in_seconds, count(distinct swapper) as num_swappers from accumilations group by date

    Run a query to Download Data