Coding questions · PayPal
Identify Users with Specific Page Navigation Patterns
This question evaluates a data scientist's competency in SQL-based sequence detection, temporal filtering, and aggregation to identify repeated navigation patterns within user-day sessions.
page_visits
+------------+---------+---------+---------------------+
| visit_date | user_id | page_id | ts |
+------------+---------+---------+---------------------+
| 2023-10-01 | 101 | A | 2023-10-01 08:00:05 |
| 2023-10-01 | 101 | B | 2023-10-01 08:05:05 |
| 2023-10-01 | 101 | D | 2023-10-01 08:15:05 |
| 2023-10-01 | 102 | A | 2023-10-01 09:00:00 |
| 2023-10-01 | 102 | C | 2023-10-01 09:10:00 |
+------------+---------+---------+---------------------+
Scenario
Web-analytics team wants to understand specific navigation behavior on the site.
Question
Write SQL that returns user_id(s) who, within the same calendar day, visited page 'A' and later page 'B' at least once, never visited page 'C' that day, and completed the A→B sequence more than once.
Hints
Use window functions (lead/lag or ROW_NUMBER) partitioned by user_id and visit_date, then aggregate.
Users with multiple A→B transitions and no C visits in a day
You are given a web analytics table page_visits that records page views with timestamps. Write SQL that returns distinct user_id values who, within the same calendar day, (1) visited page 'A' and later page 'B' at least once, (2) never visited page 'C' that day, and (3) completed the A→B sequence more than once on that day.
Table page_visits
| column | type | constraints |
|---|---|---|
| visit_date | DATE | NOT NULL |
| user_id | INTEGER | NOT NULL |
| page_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| page_id | ts | user_id | visit_date |
|---|---|---|---|
| A | 2023-10-01 08:00:05 | 101 | 2023-10-01 |
| B | 2023-10-01 08:05:05 | 101 | 2023-10-01 |
| D | 2023-10-01 08:15:05 | 101 | 2023-10-01 |
| A | 2023-10-01 09:00:00 | 102 | 2023-10-01 |
| C | 2023-10-01 09:10:00 | 102 | 2023-10-01 |
Expected output
Return distinct user_id values that have, on at least one day, more than one A→B transition and zero visits to page 'C'. For the provided sample data, no users meet these criteria, so the result is an empty set.
Hints
- Order events by ts and use LAG(page_id) over (PARTITION BY user_id, visit_date ORDER BY ts) to detect A→B transitions.
- Aggregate per user_id and visit_date, filter out days containing page 'C', and keep only those with more than one A→B transition; then select distinct user_id.