Identify Users with Specific Page Navigation Patterns

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at PayPal.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at PayPal during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview