Identify Users with Specific Page Navigation Patterns
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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.
Tables
page_visits(visit_date DATE, user_id INTEGER, page_id VARCHAR(10), ts TIMESTAMP)
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.