The Path Not Taken

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
hard
Seniority
L5
Asked in
Lyft

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.

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

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

page_views: 10 of 200 rows
view_idpage_urluser_idreferrerdur_msdeviceviewed_at
5053/products1555amazon.com637desktop2026-09-02 01:09:00
5106/checkout1652twitter.com774tablet2026-09-03 02:18:00
5159/profile1749github.com911Mobile2026-09-04 03:27:00
5212/search1846NULL1048DESKTOP2026-09-05 04:36:00
5265/about1943reddit.com1185mobile2026-09-06 05:45:00
5318/blog/intro1555NULLNULLdesktop2026-09-07 06:54:00
5371/pricing1652bing.com1459tablet2026-09-08 07:03:00
5424/docs/api1749news.ycombinator.com1596Mobile2026-09-09 08:12:00
5477/settings1846NULL1733DESKTOP2026-09-10 09:21:00
5530/Home1943bing.com1870mobile2026-09-11 10:30:00

Expected output

First 5 of 5 rows
user_id
NULL
197
1361
1555
1943

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
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
);

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions