The Vanishing Rows

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

L3

Asked in

Clear Capital

Problem

Return each content view's ID, user ID and username by matching content_views to users on user_id. Our dashboard built on that result shows fewer views than content_views actually holds, since some views were logged with no user attached, so be ready to explain exactly which rows fall out and why.

Some records disappear when the tables meet. Figure out why.

Tables

content_views

Columns: view_id INTEGER, content_id INTEGER, user_id INTEGER, device_id INTEGER, viewed_at TEXT, watch_seconds INTEGER

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

This is a lesson in what an equality match does with a missing key, dressed up as a dashboard bug. The query is one line; the skill being probed is whether you can explain why content_views holds 200 rows and the result holds 172. Every one of the 28 missing views has a NULL user_id, and a comparison against NULL is never true, so the match quietly discards them. The trap is treating the gap as a defect and 'fixing' it with a LEFT JOIN: the count comes back to 200, but 28 anonymous views now pose as user views with a blank username, and the real finding (views logged without a user) vanishes.

Step 1: Match each view to its user

Pair content_views with users on cv.user_id = u.user_id and keep view_id, user_id and username. users.user_id is the key of users, so each view matches at most one account and nothing fans out.

Step 2: Count what came back

The result has 172 rows against 200 in content_views. Before blaming the query, find the missing rows: the base table filtered on user_id IS NULL returns exactly 28.

Step 3: Rule out orphaned keys

A view can drop for two reasons: a NULL key, or a key with no users row. Here every non-null user_id exists in users, so the NULLs account for the whole gap. Saying that out loud turns a guess into a diagnosis.

SELECT cv.view_id, cv.user_id, u.username
FROM content_views cv
JOIN users u ON cv.user_id = u.user_id

Matched views only

JOIN users u ON cv.user_id = u.user_id returns 172 rows, each a view by a known account with a real username.

Every view, matched or not

LEFT JOIN users u ON cv.user_id = u.user_id returns 200 rows, 28 of them with username NULL. That is right for a report of all traffic and wrong here, because those rows are not user views.

Common follow-up questions

• How would you report the views with no user separately, next to the matched count? (Tests surfacing the `NULL`-key rows on purpose, with a filter on `user_id IS NULL` or a conditional count, instead of hiding them.)
• What changes if some `user_id` values in `content_views` point to accounts deleted from `users`? (Tests telling orphaned keys apart from missing keys: an anti-match against `users` finds them, and the inner match drops them too.)
• If `users` had duplicate `user_id` rows, what would happen to the view count? (Tests join fan-out: a duplicated key multiplies the matching views, so the count could exceed the base table.)