keshanAlgo dex arbitrage aggregates
Updated 2022-03-30
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
›
⌄
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