kiacryptoThe number of failed transactions by bots and users
    Updated 2022-06-05
    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
    Run a query to Download Data