select date_trunc('minute', block_timestamp) as minutes, swapper, count(*) as transactions

from solana.core.fact_swaps

where block_timestamp >= '2021-07-10'

and block_timestamp <= '2022-07-10'

group by 1, 2

having transactions > 10

),

bot_swaps as (

select date_trunc('day',block_timestamp) as date1, count(tx_id) as transactions,

count(case when succeeded = 'true' then 1 else null end) as successful_bot_txs,

count(case when succeeded = 'false' then 1 else null end) as failed_bot_txs

from solana.core.fact_swaps

where block_timestamp >= '2021-07-10'

and block_timestamp <= '2022-07-10'

and swapper in (select distinct swapper from bots)

group by 1

),

user_swaps as (select date_trunc('day',block_timestamp) as date2,

count(tx_id) as total_user_swaps,

count(case when succeeded = 'true' then 1 else null end) as successful_user_txs,

count(case when succeeded = 'false' then 1 else null end) as failed_user_txs

from solana.core.fact_swaps

where block_timestamp >= '2021-07-10'

and block_timestamp <= '2022-07-10'

and swapper not in (select distinct swapper from bots)

group by 1

select date2, successful_bot_txs, failed_bot_txs, successful_user_txs, failed_user_txs, (successful_bot_txs + failed_bot_txs + successful_user_txs + failed_user_txs) as total_swaps

from user_swaps

join bot_swaps on user_swaps.date2 = bot_swaps.date1

order by 1