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.
Quick Answer: 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.