bot_address as (

select

date_trunc('day', block_timestamp) as date,

from_address as bots,

count(distinct tx_id) as number_of_swaps

from

flipside_prod_db.thorchain.swaps

group by 1, 2 having number_of_swaps >= {{max_txs_human}}

),

all_address as (

select

date_trunc('day', block_timestamp) as date,

count(*) as number_of_all_swaps,

count(distinct from_address) as number_of_all_address

from

flipside_prod_db.thorchain.swaps

group by 1

),

bot_count as (

select

date_trunc('day', block_timestamp) as date,

count(*) as number_of_bot_swaps,

count(distinct from_address) as number_of_bot_address

from

flipside_prod_db.thorchain.swaps a

left join bot_address b on from_address = bots

where block_timestamp::date = b.date

group by 1

)

select

b.date,

number_of_bot_swaps / number_of_all_swaps as bots_rate,

(number_of_all_swaps - number_of_bot_swaps) / number_of_all_swaps as user_rate,

number_of_all_swaps,