Approach: build a set of heuristics scoring pipeline that flags likely bots by (A) very high event rate per user, (B) user_agents shared across many distinct users, (C) impossible geo-hops (fast travel between distant IP geolocations). Produce SQL to compute signals, combine into a score, and sample flagged sessions for manual validation (IP WHOIS, reverse DNS, known bot UA lists).1) High events / minute per user (rolling 1-min window)sql
WITH events_per_min AS (
SELECT
user_id,
date_trunc('minute', event_time) AS minute_ts,
COUNT(*) AS events_in_min
FROM events
GROUP BY 1,2
)
SELECT user_id, MAX(events_in_min) AS peak_events_per_min
FROM events_per_min
GROUP BY user_id
HAVING MAX(events_in_min) > 100; -- heuristic threshold
sql
SELECT user_agent,
COUNT(DISTINCT user_id) AS distinct_users,
COUNT(*) AS total_events
FROM events
GROUP BY user_agent
HAVING COUNT(DISTINCT user_id) > 50; -- heuristic threshold
sql
WITH ordered AS (
SELECT user_id, event_time, ip, g.country, g.lat, g.lon,
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time,
LAG(g.lat) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_lat,
LAG(g.lon) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_lon
FROM events e
JOIN ip_geo g ON e.ip = g.ip
)
SELECT user_id, COUNT(*) AS impossible_hops
FROM ordered
WHERE prev_time IS NOT NULL
AND EXTRACT(EPOCH FROM (event_time - prev_time)) < 300 -- e.g., <5 minutes
AND haversine(prev_lat, prev_lon, lat, lon) > 500 -- >500 km
GROUP BY user_id
HAVING COUNT(*) > 0;
sql
WITH s1 AS (...events_per_min...),
s2 AS (...ua_shared...),
s3 AS (...geo_hops...)
SELECT u.user_id,
COALESCE(p.peak_events_per_min,0) AS peak_epm,
CASE WHEN ua.distinct_users IS NOT NULL THEN 1 ELSE 0 END AS ua_shared_flag,
COALESCE(h.impossible_hops,0) AS hops,
( (LEAST(peak_epm,200)/200)*0.5 + ua_shared_flag*0.3 + LEAST(hops,10)/10*0.2 ) AS bot_score
FROM (SELECT DISTINCT user_id FROM events) u
LEFT JOIN s1 p USING(user_id)
LEFT JOIN s2 ua ON ua.user_agent = (SELECT user_agent FROM events WHERE user_id = u.user_id ORDER BY event_time DESC LIMIT 1)
LEFT JOIN s3 h USING(user_id)
WHERE bot_score > 0.5;
