# The Path Not Taken

> Some found the new path without ever walking the old one.

Canonical URL: <https://datadriven.io/problems/the_path_not_taken>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

The product team is measuring organic adoption of the new editor, which shows up in the page views under the URL 'new_editor' while the old one appears as 'classic_editor'. Find the users who reached the new editor without ever having opened the classic editor before their first new editor visit.

## Worked solution and explanation

### What this is really asking

Strip the marketing costume and this is a per-user set-membership test: for each user's first 'new_editor' visit, does any 'classic_editor' visit sit strictly before it? Anyone can compute the first new-editor date. What separates candidates is scoping the 'before' check to the SAME user and expressing it as a correlated NOT EXISTS instead of a blanket MIN comparison that silently drops users with zero classic rows. Get that wrong and every clean adopter with no classic history vanishes from the result.

---

### Break down the requirements

#### Step 1: Pin each user's first new-editor visit

GROUP BY user_id over page_url='new_editor', taking MIN(viewed_at). Users who never touched the new editor drop out here, which is exactly what we want.

#### Step 2: Reject if any classic visit came earlier

Correlated NOT EXISTS on page_url='classic_editor' AND viewed_at < first_new_date. Strict '<', not '<=', so a classic visit at the exact same timestamp does not disqualify.

---

### The solution

**THE PATH NOT TAKEN**

```sql
WITH first_new AS (
  SELECT user_id, MIN(viewed_at) AS first_new_date
  FROM page_views
  WHERE page_url = 'new_editor'
  GROUP BY user_id
)
SELECT fn.user_id
FROM first_new fn
WHERE NOT EXISTS (
  SELECT 1
  FROM page_views ua
  WHERE ua.user_id = fn.user_id
    AND ua.page_url = 'classic_editor'
    AND ua.viewed_at < fn.first_new_date
);
```

> **Cost Analysis**
>
> 800M rows, partitioned by viewed_at. The CTE scans only the 'new_editor' rows; the NOT EXISTS probe wants an index on (user_id, page_url, viewed_at) so each per-user lookup stays a short seek rather than a partition scan.

> **Interviewers Watch For**
>
> A one-pass MIN per (user_id, page_url) then a MIN(classic) < MIN(new) compare breaks for users with zero classic rows: the classic MIN is NULL, the comparison evaluates to UNKNOWN, and the row is silently dropped instead of kept. That is the single most common wrong answer on this shape.

> **Common Pitfall**
>
> Using '<=' disqualifies users whose classic and new visits share an exact timestamp, which happens with batch imports. The brief says 'before', so strict '<' is correct.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you surface users who tried the new editor and bounced back to classic afterward? _(Flip the NOT EXISTS to EXISTS: a classic_editor row with viewed_at > first_new_date.)_
- What changes if viewed_at is a date, not a timestamp? _(Same-day classic and new visits collapse to equal values; strict '<' can now drop genuine adopters.)_
- How would you make this incremental for a daily job? _(Persist first_new_date per user once frozen, then re-evaluate only users whose first_new_date lands in today's partition.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_path_not_taken)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.