The Threshold

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

General Assembly

Problem

Which pages in page_views show up most often among each visitor's first three views, taking a visitor's views in time order with the lower view_id first when two share a timestamp? Page addresses were logged with inconsistent capitalization, so '/Home' and '/home' count as one page, reported in lowercase. A page belongs in the answer when its appearance count is one of the three highest counts, however many pages share each of those counts.

Every visit begins with three doors. Find the ones most often opened.

Tables

page_views

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

Expected output

Worked solution and explanation

What this problem is really testing

This is a per-user ranking problem wearing a popularity-metric costume. Anyone can count pages and sort them; the separating move is PARTITION BY user_id on ROW_NUMBER(), which gives every visitor their own 1, 2, 3 sequence so you count opening pages instead of all-time traffic. Miss the partition and you number views globally: the result becomes whatever the earliest visitors happened to click. End on LIMIT 3 instead of ranking the counts and a page tied for third quietly disappears.

Break down the requirements

Step 1: Number views per visitor by viewed_at, then view_id

Write ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY viewed_at, view_id) so each visitor gets a 1, 2, 3 sequence independent of every other visitor. The view_id key matters only when two of a visitor's views share a timestamp; without it, which page lands in slot three is up to the engine, and the answer can change between runs.

Step 2: Fold page_url to one spelling

The log stored the same page under several capitalizations, so emit LOWER(page_url) in the first CTE. Grouping on the raw string splits '/Home' and '/home' into two half-sized counts, and a page that belongs at the top can fall out of the answer entirely.

Step 3: Filter to the first three before counting

Keep only view_order <= 3, then GROUP BY page_url with COUNT(*) to get appearance_count. Filtering before the aggregate is the whole point: a checkout or settings page that every visitor reaches on view ten would otherwise dominate the tally.

Step 4: Rank the counts, not the pages

DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) numbers the count values, not the pages: equal counts share a number and the next count takes the next number. Keeping rnk <= 3 therefore keeps every page holding one of the three highest counts, however many pages crowd into each level.

Cut at three rows or positions

LIMIT 3 returns exactly three rows and slices arbitrarily through a tie. RANK() <= 3 skips numbers after a tie, so counts of 10, 10, 10, 9 stop at the 10s and the 9 vanishes.

Cut at three count levels

DENSE_RANK() <= 3 on the same counts keeps the 10s, the 9 and the next count down: three levels, every page at each level, deterministic every run.

The solution

WITH ranked_views AS (
  SELECT user_id,
         LOWER(page_url) AS page_url,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY viewed_at ASC, view_id ASC) AS view_order
  FROM page_views
),
page_counts AS (
  SELECT page_url, COUNT(*) AS appearance_count,
         DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM ranked_views
  WHERE view_order <= 3
  GROUP BY page_url
)
SELECT page_url, appearance_count
FROM page_counts
WHERE rnk <= 3

Common follow-up questions

• How would you handle users who only ever viewed one page? (They contribute fewer rows to the prefix and `COUNT(*)` still works. No special case needed, but interviewers may probe whether you noticed.)
• What changes if 'first 3' should be 'first 3 different pages'? (Collapse to one row per (`user_id`, `page_url`) with `MIN(viewed_at)` before numbering, so a reload does not burn a prefix slot.)
• Would you recompute this nightly or maintain it incrementally? (A first-N-per-user metric depends on each user's earliest rows, so an incremental aggregate has to track each user's current prefix. Most teams recompute against a partitioned table.)