swap_data as (

select

from_address as user,

tx_id,

block_timestamp,

pool_name

from thorchain.swaps

union all

select

native_to_address as user,

tx_id,

block_timestamp,

pool_name

from thorchain.swaps

),

swap_range as (

select

user,

count(distinct pool_name) as pools,

min(block_timestamp) as first_swap,

max(block_timestamp) as last_swap,

datediff(day,first_swap,last_swap) as lifetime,

count(distinct tx_id) as swaps,

case when lifetime = 0 then swaps else swaps/lifetime end as swaps_per_day

from swap_data

group by 1

),

data as (

select *,

case

when swaps_per_day > 10 and pools > 1 then 'multi-pool bot'

when swaps_per_day > 10 then 'single-pool bot'

else 'normal user'