Identify and Flag Bot Traffic in Online Forum
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
PVE
+----------+-----------+
| memberId | timestamp |
+----------+-----------+
| 101 | 169100123 |
| 102 | 169100225 |
| 101 | 169100300 |
| 999 | 169101000 |
| 888 | 169101050 |
+----------+-----------+
##### Scenario
You are analyzing PageViewEvents (PVE) from an online forum to detect automated traffic.
##### Question
Write both SQL and Python scripts that identify bot users in PVE and delete (or flag) their events. You may choose any clear, workable bot definition (e.g., >N events in 1 minute, 24-hour activity, etc.). Explain your reasoning briefly.
##### Hints
Pick a simple heuristic like requests per minute threshold or 24-hour nonstop activity; aggregate by memberId and timestamp.
Overview: This question evaluates the candidate's ability to manipulate time-series event data and implement heuristic-based anomaly detection using SQL and Python, focusing on identifying and handling automated bot traffic.
Identify bot users (180s)
Using table PVE, return distinct memberIds considered bots under the heuristic: a member is a bot if they have 2 or more events within any rolling 180-second window. Order by memberId ascending.
Tables
PVE(memberId INTEGER, timestamp INTEGER)
Hints
- Self-join PVE to count events within a time window per member.
- Use BETWEEN p1.timestamp - 180 AND p1.timestamp, then DISTINCT.
List events of bots
Return all events (memberId, timestamp) that belong to bot users under the same 180-second heuristic as in Question 1. Order by memberId, timestamp.
Tables
PVE(memberId INTEGER, timestamp INTEGER)
Hints
- Reuse the bot identification CTE and filter PVE by those memberIds.