kiacryptodaily bot rate - op
    Updated 2023-03-10
    with opti_bots as (
    select
    from_address as addr,
    date_trunc('second', block_timestamp) as sec,
    count(distinct tx_hash) as tx_count
    from optimism.core.fact_transactions
    group by 1, 2 having tx_count > 10
    ),
    opti_tx as (
    select
    date_trunc('day', block_timestamp) as day,
    count(distinct tx_hash) as tx_count
    from optimism.core.fact_transactions
    where day >= '2023-01-01'
    group by 1
    ),
    info as (
    select
    date_trunc('day', block_timestamp) as date,
    (count(distinct tx_hash) / avg(tx_count)) * 100 as tx_by_bot_rate
    from optimism.core.fact_transactions join opti_tx on block_timestamp::date = day
    where
    date >= '2023-01-01' and from_address in (select addr from opti_bots)
    group by 1
    ),
    avg_info as (
    select avg(tx_by_bot_rate) as avg_tx_by_bot_rate
    from info
    )
    select *
    from info, avg_info
    Run a query to Download Data