with bots as (
select date_trunc('minute', block_timestamp) as min, payer, count(*) as txs
from flow.core.fact_transactions
group by 1, 2
having txs > 10
),
bot_txs as (
select block_timestamp::date as date, count(distinct tx_id) as bot_failed
from flow.core.fact_transactions
where payer in (select distinct payer from bots) and tx_succeeded = false
group by 1
),
user_txs as (
select block_timestamp::date as date1, count(distinct tx_id) as user_failed
from flow.core.fact_transactions
where payer not in (select distinct payer from bots) and tx_succeeded = false
group by 1
)
select date, bot_failed, user_failed
from bot_txs, user_txs
where date = date1