RamaharArb frequency
Updated 2022-07-18
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
32
33
34
35
36
›
⌄
--Questions
-- Identify the Ethereum arb bots and show their activity over time. How many are there? What is their average swap frequency and volume?
--Which tokens are they swapping? Are they interacting with synths or non-synths? How much profit are they making?
With swaptypes as (SELECT
tx_id,
COUNT(1) AS tx
FROM flipside_prod_db.thorchain.swaps
where blockchain = 'ETH'
GROUP BY tx_id ) ,
doubleswaps as (select
s.block_timestamp, s.tx_id, s.blockchain, s.pool_name,
s.from_address, s.native_to_address,
s.from_asset, s.to_asset, s.from_amount, s.to_amount, s.from_amount_usd, s.to_amount_usd,
row_number() over (partition by s.tx_id order by s.from_asset) as row_number
from flipside_prod_db.thorchain.swaps s
inner join (select tx_id, count(1) as tx_types from flipside_prod_db.thorchain.swaps where blockchain = 'ETH' group by tx_id having tx_types = '2') f ON f.tx_id = s.tx_id ),
filtereddouble as (select
d.block_timestamp, d.tx_id, d.pool_name,
d.from_address, d.native_to_address,
d.from_asset, t.to_asset,
d.from_amount, t.to_amount,
d.from_amount_usd, t.to_amount_usd
from doubleswaps d
join (select tx_id, to_asset, to_amount, to_amount_usd from doubleswaps where row_number = '2' ) t ON d.tx_id = t.tx_id
where row_number = '1'
order by d.tx_id ),
tripleswaps as (select
s.block_timestamp, s.tx_id, s.blockchain, s.pool_name,
s.from_address, s.native_to_address,
s.from_asset, s.to_asset, s.from_amount, s.to_amount, s.from_amount_usd, s.to_amount_usd,
row_number() over (partition by s.tx_id order by s.from_asset) as row_number
from flipside_prod_db.thorchain.swaps s
inner join (select tx_id, count(1) as tx_types from flipside_prod_db.thorchain.swaps where blockchain = 'ETH' group by tx_id having tx_types = '3') f ON f.tx_id = s.tx_id ),
Run a query to Download Data