PayPal·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2024Remote

Summary

PayPal data scientist interview with a SQL question that looks straightforward until you actually sit down to write it. The sequence-detection angle tripped me up more than I expected.

Questions Asked (1)

Given a page_visits table with columns for date, user_id, page_id, and timestamp, write SQL to return distinct user_ids who visited page A followed immediately by page B on the same calendar day, where the page directly after B is NOT page C.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

I went for LEAD() pretty quickly, which was the right instinct, but I initially forgot to partition by both user_id and date together.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function like LEAD to look at the next page visit for each user on the same day, then filter for rows where the current page is A and the next page is B, and the page after that is not C. Ensure the visits are ordered by timestamp within each user and date.

Pro tip: Mention that you would verify the grain of the data and handle ties in timestamps, as simultaneous visits could affect the 'immediately followed' condition. Also, consider performance implications and indexing on (user_id, date, timestamp).

1. Understand the data and requirements

Clarify that the table records each page visit with a timestamp, and we need to find users who visited page A then immediately page B on the same day, and the page after B is not C.

2. Order visits within each user and day

Use a window function to assign a sequence or to access the next and next-next pages based on timestamp ordering, partitioned by user_id and date.

3. Identify the pattern A -> B -> not C

Filter rows where the current page is A, the next page is B, and the page after that is not C (or is NULL).

4. Return distinct user_ids

Select distinct user_ids from the filtered result to get the final list of users who meet the criteria.

5. Consider edge cases and performance

Discuss handling of ties in timestamps, users with multiple qualifying sequences, and potential optimizations like indexing.

Key Points to Mention

Use of window functions (LEAD) to access subsequent page visits
Partitioning by user_id and date to ensure same-day visits
Ordering by timestamp to define 'immediately followed'
Filtering conditions: current page = A, next page = B, next-next page != C or IS NULL
Handling ties in timestamps (e.g., using additional ordering or deduplication)
Performance considerations: indexing on (user_id, date, timestamp) and avoiding unnecessary sorting

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.