Orion_9RPercentage of Swaps Completed by Bots and Humans, last 60 days
Updated 2023-03-26Copy Reference Fork
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
›
⌄
with bots as (
select date_trunc('minute',block_timestamp) as minute,
from_address as trader,
count(*) as txs -- double swaps are counted as two swaps
from thorchain.swaps
where block_timestamp::date >= current_date - 61 -- using last 60 days of data
group by 1, 2
having txs > 10 -- purpose is to filter for addresses that have done more than 10 swaps in a single minute.
)
select
count(distinct (tx_id)) as txs,-- using distinct tx_id. Double swaps will count as once transaction
sum(LIQ_FEE_RUNE_USD + LIQ_FEE_ASSET_USD) as fees_paid,
sum(TO_AMOUNT_USD) as swap_volume_usd, -- net of fees
count(distinct(FROM_ADDRESS)) as nb_swappers, --nb of bot swappers
'bots' as account_type
from thorchain.swaps
where block_timestamp::date > current_date - 61
and block_timestamp::date < current_date
and from_address in (select distinct trader from bots)
--group by 1
union
Select
count(distinct(tx_id)) as txs,
sum(LIQ_FEE_RUNE_USD + LIQ_FEE_ASSET_USD) as fees_paid,
sum(TO_AMOUNT_USD) as swap_volume_usd, -- net of fees
count(distinct(FROM_ADDRESS)) as nb_swappers, --nb of human swappers
'humans' as account_type
from thorchain.swaps
where block_timestamp::date > current_date - 61
and block_timestamp::date < current_date
and from_address not in (select distinct trader from bots)
group by account_type
Run a query to Download Data