Identify and Flag Bot Traffic in Online Forum

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.

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.

Table PVE

column	type	constraints
memberId	INTEGER
timestamp	INTEGER

Sample data:

memberId	timestamp
101	169100123
102	169100225
101	169100300
999	169101000
888	169101050

Expected output

Using the heuristic 'a member is a bot if they have >= 2 events within any 180-second window', the query returns the list of bot memberIds. On the provided data, memberId 101 has two events 177 seconds apart, so it is flagged.

memberId
101

Hints

1. Self-join PVE to count events within a time window per member.
2. 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.

Table PVE

column	type	constraints
memberId	INTEGER
timestamp	INTEGER

Sample data:

memberId	timestamp
101	169100123
102	169100225
101	169100300
999	169101000
888	169101050

Expected output

Events belonging to members identified as bots by the 180-second heuristic. On the provided data, only memberId 101 is a bot, so both of their events are listed.

memberId	timestamp
101	169100123
101	169100300

Hints

1. Reuse the bot identification CTE and filter PVE by those memberIds.