date_trunc('hour', block_timestamp) as hour,

from_address,

count(distinct(tx_id)) as count_swap

from thorchain.swaps

group by 1,2

having count_swap >= 50),

base as (select

date_trunc('month', block_timestamp) as month,

pool_name,

split(pool_name, '-')[0] as pool_names,

split(pool_name, '.')[0] as chain_names,

case

when from_address in (select from_address from botters) then 'Botters'

else 'Typical_swapper'

end as type_swapper,

sum(to_amount_usd) as swap_volume,

count(distinct(tx_id)) as count_swap,

count(distinct(from_address)) as count_swappers

from thorchain.swaps

where month >= '2022-01-01'

group by 1,2,5)

select month,

type_swapper,

sum(swap_volume) as swap_volume,

sum(count_swap) as count_swap,

sum(count_swappers) as count_swappers

from base

group by 1,2

order by 3 desc