Coding questions · PayPal

Identify Users with Specific Page Visit Sequence

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.

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.