-- 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 , tx_hash , block_timestamp , to_address , value , gas_price , prev_timestamp , case when prev_timestamp is not null then datediff('second', prev_timestamp, block_timestamp) else null end as seconds_since_last_tx from tx_with_lag ), -- Volume-based metrics volume_metrics as ( select from_address as address , count(distinct tx_hash) as total_txs , count(distinct date_trunc('day', block_timestamp)) as active_days_volume , min(block_timestamp) as first_tx , max(block_timestamp) as last_tx , avg(seconds_since_last_tx) as avg_time_between_txs , stddev(seconds_since_last_tx) as stddev_time_between_txs , count(distinct to_address) / nullif(count(*), 0) as unique_destination_ratio , count(distinct date_trunc('hour', block_timestamp)) as active_hours , min(seconds_since_last_tx) as min_time_between_txs , avg(value) as avg_tx_value , stddev(value) as stddev_tx_value , count(case when value = 0 then 1 end) / nullif(count(*), 0) as zero_value_ratio from tx_with_diffs group by 1 ), first_activity as ( select from_address as address , min(block_timestamp) as first_ever_tx , datediff('day', min(block_timestamp), current_date) as account_age_days from base.core.fact_transactions group by 1 ), active_days as ( select from_address as address , count(date_trunc(day, block_timestamp)) as total_active_days , count(tx_hash) as total_txs_all from base.core.fact_transactions group by 1 ), activity_streaks as ( select address , tx_date , cast(cast(tx_date as date) - row_number() over (partition by address order by tx_date) as date) as streak_group from daily_activity ), streak_lengths as ( select address , streak_group , count(*) as streak_length , min(tx_date) as streak_start , max(tx_date) as streak_end from activity_streaks group by 1, 2 ), daily_activity_gaps as ( select a.address , a.tx_date , datediff( 'day' , lag(a.tx_date) over (partition by a.address order by a.tx_date) , a.tx_date ) as day_gap from daily_activity a ), -- DAU-focused metrics dau_metrics as ( select address , count(distinct tx_date) as active_days , avg(txs_per_day) as avg_txs_per_active_day , stddev(txs_per_day) as stddev_txs_per_day , sum(txs_per_day) as total_txs_dau , sum(case when txs_per_day = 1 then 1 else 0 end) as days_with_exactly_one_tx , stddev(hour_of_first_tx) as time_of_day_stddev , avg(activity_span_seconds) as avg_daily_activity_span , avg(unique_destinations_per_day) as avg_unique_destinations_per_day , sum(unique_destinations_per_day) / nullif(sum(txs_per_day), 0) as total_unique_destination_ratio , avg(avg_gas_price_for_day) as avg_gas_price , stddev(avg_gas_price_for_day) as stddev_gas_price , stddev(avg_gas_price_for_day) / nullif(avg(avg_gas_price_for_day), 0) as gas_price_variation , avg(avg_tx_value_for_day) as avg_tx_value_dau , stddev(avg_tx_value_for_day) as stddev_tx_value_dau , count(distinct dayofweek(tx_date)) as distinct_days_of_week , count(distinct dayofmonth(tx_date)) as distinct_days_of_month from daily_activity group by 1 ), -- Orchestrated activity detection activity_clusters as ( select date_trunc('hour', first_ever_tx) as creation_hour , count(distinct address) as new_addresses_in_hour from first_activity group by 1 having count(distinct address) > 10 ), -- Combined metrics combined_metrics as ( select coalesce(v.address, d.address) as address -- Volume metrics , coalesce(v.total_txs, 0) as total_txs , coalesce(v.active_days_volume, 0) as active_days_volume , v.avg_time_between_txs , v.stddev_time_between_txs , v.unique_destination_ratio , v.min_time_between_txs , v.zero_value_ratio -- DAU metrics , coalesce(d.active_days, 0) as active_days , d.avg_txs_per_active_day , d.days_with_exactly_one_tx , d.time_of_day_stddev , d.gas_price_variation , d.avg_unique_destinations_per_day -- Streak metrics , max(s.streak_length) as max_consecutive_days , count(distinct s.streak_group) as number_of_streaks -- Gap metrics , avg(g.day_gap) as avg_gap_between_active_days , stddev(g.day_gap) as stddev_gap_between_active_days -- Account info , f.first_ever_tx , f.account_age_days -- Cluster info , case when c.creation_hour is not null then 1 else 0 end as part_of_creation_cluster , coalesce(c.new_addresses_in_hour, 0) as cluster_size , d.distinct_days_of_week , a.total_active_days , a.total_txs_all from volume_metrics v full outer join dau_metrics d on v.address = d.address left join first_activity f on coalesce(v.address, d.address) = f.address left join streak_lengths s on coalesce(v.address, d.address) = s.address left join daily_activity_gaps g on coalesce(v.address, d.address) = g.address left join activity_clusters c on date_trunc('hour', f.first_ever_tx) = c.creation_hour left join active_days a on coalesce(v.address, d.address) = a.address group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 19, 20, 21, 22, 23, 24, 25 ), -- Final scoring final_scores as ( select * -- Volume-based bot score (0-100) , ( case when total_txs > 2000 then 30 when total_txs > 1000 then 25 when total_txs > 500 then 20 when total_txs > 200 then 15 when total_txs > 100 then 10 else 5 end + case when stddev_time_between_txs < 5 then 25 when stddev_time_between_txs < 10 then 20 when stddev_time_between_txs < 30 then 15 when stddev_time_between_txs < 60 then 10 else 5 end + case when unique_destination_ratio < 0.01 then 20 when unique_destination_ratio < 0.05 then 15 when unique_destination_ratio < 0.1 then 10 when unique_destination_ratio < 0.2 then 5 else 0 end + case when total_txs / nullif(active_days_volume, 0) > 200 then 15 when total_txs / nullif(active_days_volume, 0) > 100 then 12 when total_txs / nullif(active_days_volume, 0) > 50 then 8 when total_txs / nullif(active_days_volume, 0) > 25 then 5 else 0 end + case when min_time_between_txs < 1 then 10 when min_time_between_txs < 5 then 8 when min_time_between_txs < 10 then 5 else 0 end ) as volume_bot_score -- DAU-inflating bot score (0-100+) , ( (days_with_exactly_one_tx / nullif(active_days, 0) * 50) + (case when time_of_day_stddev < 2 then 20 else 0 end) + (case when stddev_gap_between_active_days < 1 and avg_gap_between_active_days > 1 then 20 else 0 end) + (case when max_consecutive_days > 14 and avg_txs_per_active_day < 3 then 30 else 0 end) + (case when part_of_creation_cluster = 1 then 15 else 0 end) + (case when gas_price_variation < 0.05 and active_days > 5 then 15 else 0 end) + (case when avg_unique_destinations_per_day = 1 and active_days > 7 then 20 else 0 end) + (case when account_age_days < 30 and active_days > 10 then 10 else 0 end) ) as dau_bot_score -- Legitimate user score (0-100) , ( -- Account maturity (0-25 points) case when account_age_days > 365 then 25 when account_age_days > 180 then 20 when account_age_days > 90 then 15 when account_age_days > 30 then 10 else 5 end + -- Activity diversity (0-20 points) case when unique_destination_ratio > 0.7 then 20 when unique_destination_ratio > 0.5 then 15 when unique_destination_ratio > 0.3 then 10 when unique_destination_ratio > 0.1 then 5 else 0 end + -- Natural timing patterns (0-15 points) case when time_of_day_stddev > 6 then 15 when time_of_day_stddev > 4 then 12 when time_of_day_stddev > 3 then 8 when time_of_day_stddev > 2 then 5 else 0 end + -- Irregular activity patterns (0-15 points) case when stddev_gap_between_active_days > 5 then 15 when stddev_gap_between_active_days > 3 then 12 when stddev_gap_between_active_days > 2 then 8 when stddev_gap_between_active_days > 1 then 5 else 0 end + -- Reasonable transaction frequency (0-10 points) case when avg_txs_per_active_day between 2 and 20 then 10 when avg_txs_per_active_day between 1 and 50 then 8 when avg_txs_per_active_day between 0.5 and 100 then 5 else 0 end + -- Gas price sensitivity (0-10 points) case when gas_price_variation > 0.3 then 10 when gas_price_variation > 0.2 then 8 when gas_price_variation > 0.1 then 5 when gas_price_variation > 0.05 then 3 else 0 end + -- Weekend activity diversity (0-5 points) case when distinct_days_of_week >= 6 then 5 when distinct_days_of_week >= 4 then 3 when distinct_days_of_week >= 2 then 1 else 0 end ) as legitimate_user_score from combined_metrics ), final_evaluation as ( select address , total_txs , total_txs_all , active_days , total_active_days , avg_txs_per_active_day , days_with_exactly_one_tx , days_with_exactly_one_tx / nullif(active_days, 0) as pct_days_with_single_tx , max_consecutive_days , account_age_days , part_of_creation_cluster , cluster_size , time_of_day_stddev , avg_gap_between_active_days , stddev_gap_between_active_days , avg_time_between_txs , stddev_time_between_txs , unique_destination_ratio , min_time_between_txs , zero_value_ratio , gas_price_variation -- Individual scores , volume_bot_score , dau_bot_score , legitimate_user_score -- Combined comprehensive scores , greatest(volume_bot_score, dau_bot_score) as max_bot_score , (volume_bot_score + dau_bot_score) / 2 as avg_bot_score -- Net legitimacy score (positive = more human-like, negative = more bot-like) , legitimate_user_score - greatest(volume_bot_score, dau_bot_score) as net_legitimacy_score -- Confidence score (how sure we are about the classification) , abs(legitimate_user_score - greatest(volume_bot_score, dau_bot_score)) as classification_confidence -- User legitimacy classification , case when legitimate_user_score >= 80 then 'Highly Legitimate User' when legitimate_user_score >= 70 then 'Very Likely Legitimate' when legitimate_user_score >= 60 then 'Likely Legitimate' when legitimate_user_score >= 50 then 'Possibly Legitimate' when legitimate_user_score >= 40 then 'Questionable Legitimacy' when legitimate_user_score >= 30 then 'Low Legitimacy' else 'Minimal Legitimacy Indicators' end as user_legitimacy_classification -- Determine primary classification , case when legitimate_user_score >= 70 and greatest(volume_bot_score, dau_bot_score) <= 30 then 'Verified Human' when legitimate_user_score >= 60 and greatest(volume_bot_score, dau_bot_score) <= 40 then 'Likely Human' when legitimate_user_score >= 50 and greatest(volume_bot_score, dau_bot_score) <= 50 then 'Probable Human' when abs(legitimate_user_score - greatest(volume_bot_score, dau_bot_score)) <= 15 then 'Uncertain/Needs Review' when volume_bot_score > dau_bot_score + 20 and volume_bot_score > 50 then 'Volume Bot' when dau_bot_score > volume_bot_score + 20 and dau_bot_score > 50 then 'DAU Bot' when volume_bot_score > 60 and dau_bot_score > 60 then 'Hybrid Bot' when greatest(volume_bot_score, dau_bot_score) > 65 then 'Confirmed Bot' when greatest(volume_bot_score, dau_bot_score) > 50 then 'Probable Bot' when greatest(volume_bot_score, dau_bot_score) > 35 then 'Possible Bot' else 'Likely Human' end as primary_classification -- Filtering recommendation for bot elimination , case when legitimate_user_score >= 60 and greatest(volume_bot_score, dau_bot_score) <= 40 then 'KEEP - Legitimate User' when legitimate_user_score >= 50 and greatest(volume_bot_score, dau_bot_score) <= 50 then 'KEEP - Probable Human' when abs(legitimate_user_score - greatest(volume_bot_score, dau_bot_score)) <= 15 then 'REVIEW - Uncertain' when greatest(volume_bot_score, dau_bot_score) >= 65 then 'REMOVE - Confirmed Bot' when greatest(volume_bot_score, dau_bot_score) >= 50 then 'REMOVE - Probable Bot' else 'REVIEW - Edge Case' end as filtering_recommendation from final_scores where (total_txs > 50 or active_days >= 3) -- Focus on meaningfully active accounts ) select date_trunc(day, t.block_timestamp) as day , case when primary_classification = 'Probable Bot' then 'Plausible Bot' when primary_classification = 'Possible Bot' then 'Plausible Bot' when primary_classification = 'Uncertain/Needs Review' then 'Possible Bot (Uncertain)' when primary_classification = 'Confirmed Bot' then 'Hardcore Bot' else primary_classification end as bot_classification , count(distinct address) as addresses , count(tx_hash) as total_transactions , avg(legitimate_user_score) as average_legit_score , avg(avg_bot_score) as average_bot_score , avg(net_legitimacy_score) as average_net_legit_score , round(1.0 * count(t.tx_hash) / nullif(count(distinct t.from_address), 0), 2) as avg_tx_per_bot , round(1.0 * sum(t.value_precise) / nullif(count(distinct t.from_address), 0), 2) as avg_value_per_bot , round(1.0 * sum(t.tx_fee_precise) / nullif(count(distinct t.from_address), 0), 6) as avg_fee_per_bot , round(1.0 * count(distinct t.to_address) / nullif(count(distinct t.from_address), 0), 2) as avg_contracts_per_bot from final_evaluation f left join base.core.fact_transactions t on t.from_address = f.address where primary_classification in ('Probable Bot', 'Possible Bot', 'Volume Bot', 'DAU Bot', 'Hybrid Bot', 'Confirmed Bot', 'Uncertain/Needs Review') and block_timestamp >= current_date - interval '30 days' group by 1, 2 order by day desc , case when bot_classification = 'Hardcore Bot' then 1 when bot_classification = 'Hybrid Bot' then 2 when bot_classification = 'Volume Bot' then 3 when bot_classification = 'DAU Bot' then 4 when bot_classification = 'Plausible Bot' then 5 when bot_classification = 'Possible Bot (Uncertain)' then 6 end