# The Vanishing Rows > Some records disappear when the tables meet. Figure out why. Canonical URL: 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. ## Tables ### content_views - view_id INTEGER - content_id INTEGER - user_id INTEGER - device_id INTEGER - viewed_at TEXT - watch_seconds INTEGER Sample rows (10 of 200): | view_id | content_id | user_id | device_id | viewed_at | watch_seconds | | --- | --- | --- | --- | --- | --- | | 7053 | 372 | 1361 | 404 | 2026-02-02 01:07:00 | 41 | | 7106 | 501 | 2622 | 607 | 2026-03-03 02:14:00 | 72 | | 7159 | 630 | 3883 | 810 | 2026-04-04 03:21:00 | 103 | | 7212 | 759 | 5144 | 1013 | 2026-05-05 04:28:00 | 134 | | 7265 | 888 | 6405 | 1216 | 2026-06-06 05:35:00 | 165 | | 7318 | 1017 | 7666 | 1419 | 2026-07-07 06:42:00 | 196 | | 7371 | 1146 | NULL | 1622 | 2026-08-08 07:49:00 | 227 | | 7424 | 1275 | 488 | 1825 | 2026-09-09 08:56:00 | 258 | | 7477 | 1404 | 1749 | NULL | 2026-01-10 09:03:00 | 289 | | 7530 | 1533 | 3010 | 2231 | 2026-02-11 10:10:00 | 320 | ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 5 of 172 rows: | view_id | user_id | username | | --- | --- | --- | | 7053 | 1361 | bella_q | | 7106 | 2622 | derek | | 7159 | 3883 | felix | | 7212 | 5144 | haruki | | 7265 | 6405 | jolene | ## 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. **Inner join showing NULL exclusion** ```sql SELECT cv.view_id, cv.user_id, u.username FROM content_views cv JOIN users u ON cv.user_id = u.user_id ``` > **`NULL` never equals anything** > > When `cv.user_id` is `NULL`, the condition `cv.user_id = u.user_id` evaluates to unknown, not true, and a join keeps only rows where its condition is true. No user row can rescue it, not even another `NULL`. **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. > **A restored count is not a fix** > > Swapping in a `LEFT JOIN` because the total then matches expectations hides the data-quality issue the question is about: 14% of views carry no `user_id` at all, and that is what the dashboard owners need to hear. > **Diagnose with counts, not a theory** > > Strong candidates quantify. Compare `COUNT(*)` on `content_views`, `COUNT(user_id)` on the same table, and the joined row count: 200, 172, 172. Because `COUNT(user_id)` skips NULLs and already lands on the joined number, the entire gap is `NULL` keys. > **One hash join, no extra pass** > > `users` is the small side (200 rows here, millions in production), so it builds the hash table and `content_views` streams through it. A row with a `NULL` key can be skipped before probing, so the dropped views cost nothing. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_vanishing_rows) - [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.