i_danBase: daily bot segmentation copy
    Updated 2025-05-31
    -- Comprehensive Bot Detection: Volume-based + DAU-inflating patterns
    with daily_activity as (
    select
    from_address as address
    , date_trunc('day', block_timestamp) as tx_date
    , count(*) as txs_per_day
    , count(distinct to_address) as unique_destinations_per_day
    , min(block_timestamp) as first_tx_of_day
    , max(block_timestamp) as last_tx_of_day
    , datediff('second', min(block_timestamp), max(block_timestamp)) as activity_span_seconds
    , avg(gas_price) as avg_gas_price_for_day
    , stddev(gas_price) as stddev_gas_price_for_day
    , avg(value) as avg_tx_value_for_day
    , extract(hour from min(block_timestamp)) as hour_of_first_tx
    from base.core.fact_transactions
    where block_timestamp >= current_date - interval '30 days'
    group by 1, 2
    ),

    -- Transaction-level analysis for volume-based detection
    tx_with_lag as (
    select
    from_address
    , tx_hash
    , block_timestamp
    , to_address
    , value
    , gas_price
    , lag(block_timestamp) over(partition by from_address order by block_timestamp) as prev_timestamp
    from base.core.fact_transactions
    where block_timestamp >= current_date - interval '30 days'
    ),

    tx_with_diffs as (
    select
    from_address
    Last run: about 1 month ago
    DAY
    BOT_CLASSIFICATION
    ADDRESSES
    TOTAL_TRANSACTIONS
    AVERAGE_LEGIT_SCORE
    AVERAGE_BOT_SCORE
    AVERAGE_NET_LEGIT_SCORE
    AVG_TX_PER_BOT
    AVG_VALUE_PER_BOT
    AVG_FEE_PER_BOT
    AVG_CONTRACTS_PER_BOT
    1
    2025-05-31 00:00:00.000Hardcore Bot3263348034.91869863.781502329002-37.704583651434102.70.470.001313.39
    2
    2025-05-31 00:00:00.000Hybrid Bot2178230724.79220574.433049200554-53.952071348731379.290.050.0026430.27
    3
    2025-05-31 00:00:00.000Volume Bot6354443957133.77978756.073262684604-46.40338875085698.72.630.0049446.44
    4
    2025-05-31 00:00:00.000DAU Bot620068722648.16422946.715601034955-28.5220465428891.410.040.0000050.11
    5
    2025-05-31 00:00:00.000Plausible Bot28976273530.87796347.090774251614-24.27281548099121.660.180.0000744.06
    6
    2025-05-31 00:00:00.000Possible Bot (Uncertain)5446253545351.57930839.331759578712-2.5173401020269.830.380.0000313.68
    7
    2025-05-30 00:00:00.000Hardcore Bot3665157235.41883263.649655655685-36.982462000698140.910.830.0014244.65
    8
    2025-05-30 00:00:00.000Hybrid Bot22910450025.2902374.511307792344-53.668025280861456.330.210.0033220.28
    9
    2025-05-30 00:00:00.000Volume Bot8354622693534.18654855.635667437242-45.127213629177745.385.10.0082486.04
    10
    2025-05-30 00:00:00.000DAU Bot6834110099548.50890647.11223994876-28.3841115624541.483.690.0000070.13
    11
    2025-05-30 00:00:00.000Plausible Bot329610470229.53095545.556740373871-24.72463803461331.770.160.00016113.92
    12
    2025-05-30 00:00:00.000Possible Bot (Uncertain)6494683118852.56363239.194586920468-1.57362693289612.80.540.0000575.21
    13
    2025-05-29 00:00:00.000Hardcore Bot3694503935.14547463.651645978485-37.19700894225122.060.580.00168611.35
    14
    2025-05-29 00:00:00.000Hybrid Bot23612206622.09099276.081927108695-57.130517627349517.230.050.0032960.28
    15
    2025-05-29 00:00:00.000Volume Bot11585847197632.52738553.745880619993-45.28795513585731.294.240.0095833.22
    16
    2025-05-29 00:00:00.000DAU Bot7322616377840.41887846.509344977195-29.3189622742372.240.030.000020.31
    17
    2025-05-29 00:00:00.000Plausible Bot410813521723.79159442.963134976371-27.8765404915832.920.310.0002062.73
    18
    2025-05-29 00:00:00.000Possible Bot (Uncertain)80011152914548.14533238.057468559195-4.39519889451319.110.390.0001393.86
    19
    2025-05-28 00:00:00.000Hardcore Bot5316775329.1445165.525336684353-45.455080897525127.60.410.0015742.39
    20
    2025-05-28 00:00:00.000Hybrid Bot4468904924.90611974.259846101023-53.866650628306199.660.110.0015910.14
    ...
    186
    22KB
    344s