select block_timestamp,

tx_id,

from_address as trader,

pool_name,

concat_ws (' to ', from_asset, to_asset) as swap_pair,

from_amount_usd,--token_0_amount_usd,

to_amount_usd

from thorchain.swaps

where block_timestamp >= CURRENT_DATE - {{last_days}} - 1

and block_timestamp < CURRENT_DATE - 1

and swap_pair is not NULL

),

daily_count as (

select block_timestamp::date as fecha,

trader,

count (distinct tx_id) as txs

from swaps

group by 1,2

order by 3 desc

),

max_number as (

select trader,

max (txs) as max_txs

from daily_count

group by 1

),

BOT_SWAPS as (

select swaps.block_timestamp::date as fecha,

count (swaps.tx_id) as number_of_swaps,

sum (from_amount_usd) as sum_from_usd,

sum (to_amount_usd) as sum_to_usd

from swaps

inner join max_number

on swaps.trader = max_number.trader