Identify Users with Specific Page Visit Sequence
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
page_visits
+------------+---------+---------+----------+
| date | user_id | page_id | ts |
+------------+---------+---------+----------+
|2024-06-01 | 101 | A |08:00:01 |
|2024-06-01 | 101 | B |08:05:10 |
|2024-06-01 | 101 | D |08:07:00 |
|2024-06-01 | 102 | A |09:00:00 |
|2024-06-01 | 102 | B |09:02:00 |
+------------+---------+---------+----------+
##### Scenario
Website analytics team wants to find users who, within one calendar day, visited page A followed by page B where the first page immediately after B is NOT page C.
##### Question
Given table page_visits(date, user_id, page_id, ts), write SQL to return distinct user_ids who, on the same date, have the sequence A → B with no C directly after that B (A→B and A→B→D→C qualify; A→B→C does not).
##### Hints
Use LEAD or ROW_NUMBER to look at the next page after each B, filter, then group.
Overview: This question evaluates event-sequence detection and temporal ordering skills in SQL, focusing on concepts such as windowing, adjacent-row reasoning, and session-scoped grouping to identify ordered page visits.
Given table page_visits(date, user_id, page_id, ts), return distinct user_ids who, on the same date, have the sequence A → B where the first page immediately after that B is NOT C. A→B and A→B→D→C qualify; A→B→C does not.
Tables
page_visits(date DATE, user_id INTEGER, page_id VARCHAR(1), ts TIME)
Hints
- Use LEAD or ROW_NUMBER to look at the next page after each B, filter, then group.
- Partition window functions by user_id and date and order by ts to evaluate sequences.