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
),
top10 as (
select payer, count(*) as fail_tx
from flow.core.fact_transactions
where tx_succeeded = false
group by 1 having fail_tx > 100
order by 2 desc
limit 10
)
select count(*) as bots
from top10
where payer in (select payer from bots)