Least Viewed Content

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

TikTok

Problem

Find every page in page_views whose unique viewer count is within one of our least-viewed page's, fewest viewers first: that whole tail is our pruning shortlist. Our URL paths are case-sensitive, so /Home and /home are separate pages.

Nobody is watching. Should it still exist?

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

This is a floor-relative filter dressed up as content pruning. The shortlist is not 'pages with few viewers'; it is every page within one unique viewer of the least-viewed page, and that floor only exists after every page has been counted. So the real skill is an aggregate of an aggregate: count unique visitors per page_url, take the MIN of those counts, then keep every page at or below that floor plus one. Anyone writes the GROUP BY. What separates candidates is deriving the floor from all pages instead of grabbing one bottom row with LIMIT 1, and counting people instead of rows. Count rows and every page sits at 14 or 16 views, the floor becomes 14, and twelve of fourteen pages land on the pruning list, healthy ones included.

Build it in two levels

Step 1: Count people per page_url, not rows

GROUP BY page_url with COUNT(DISTINCT user_id). Every page/visitor pair shows up at least twice in page_views, so a row count measures reload habits, not audience. Group on page_url exactly as stored: the paths are case-sensitive, so '/Home' and '/home' are two pages.

Step 2: Derive the floor from every page's count

The floor is the smallest of the per-page counts, an aggregate over an aggregate. Standard SQL rejects MIN(COUNT(DISTINCT user_id)), so the per-page counts go into a derived table, page_counts, and MIN(viewer_count) reads it. On this data the floor is 4.

Step 3: Filter the groups in HAVING

WHERE runs before grouping and cannot see COUNT(DISTINCT user_id), so the comparison against the floor plus one lives in HAVING. That keeps two pages at 4 viewers and three at 5.

Step 4: Sort fewest viewers first

ORDER BY unique_viewers puts the deadest pages at the top. Pages tied on a count can come back in any order, so no tiebreaker is needed.

SELECT page_url,
       COUNT(DISTINCT user_id) AS unique_viewers
FROM page_views
GROUP BY page_url
HAVING COUNT(DISTINCT user_id) <= (
  SELECT MIN(viewer_count)
  FROM (
    SELECT COUNT(DISTINCT user_id) AS viewer_count
    FROM page_views
    GROUP BY page_url
  ) AS page_counts
) + 1
ORDER BY unique_viewers

Counting rows

COUNT(*) gives every page 14 or 16 views because each visit pattern repeats. The floor becomes 14, the tail rule admits everything at 14 or 15, and 12 of 14 pages are flagged for pruning.

Counting unique visitors

COUNT(DISTINCT user_id) spreads pages from 4 to 7 viewers. The floor is 4, and exactly five pages make the shortlist: '/home', '/docs/api', '/search', '/Home' and 'new_editor'.

Where the interviewer goes next

Common follow-up questions

• Restrict the shortlist to the last 30 days of views. (Tests whether the same date filter lands in both levels, the outer query and `page_counts`, or the floor comes from a different population.)
• Rewrite it so `page_views` is scanned once. (Probes a grouped intermediate result plus `MIN(unique_viewers) OVER ()`.)
• Return the shortlist per `device` instead of overall. (The floor becomes per device, and 'Mobile' versus 'mobile' forces a decision about normalizing `device`.)
• What if anonymous views should count as viewers? (Checks how `COUNT(DISTINCT ...)` treats `NULL` and what key stands in for a missing `user_id`.)