The Row Count Surprise

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

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
Dell Technologies

Problem

The data quality team is auditing how a users-vs-ad_impressions join behaves under the three classic semantics. Some users were never served an impression, and some impressions don't tie back to a known user. In a single result set, surface three rows labeled inner_join, left_join, and full_outer_join, each with the total row count that combination produces.

Same tables. Different handshakes. Wildly different results.

Tables

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

ad_impressions: 10 of 200 rows
impression_iduser_idad_campaignimpression_timeclickedrevenue
253100BRAND_AWARENESS_Q12026-02-02 01:07:000NULL
306197PRODUCT_LAUNCH_X2026-03-03 02:14:000NULL
359294RETARGETING_CART2026-04-04 03:21:000NULL
412391HOLIDAY_PROMO2026-05-05 04:28:000NULL
465488NEW_USER_ACQU2026-06-06 05:35:0010.2
518NULLLOYALTY_PROGRAM2026-07-07 06:42:000NULL
571682FLASH_SALE_48H2026-08-08 07:49:000NULL
624779SUMMER_SALE_20242026-09-09 08:56:000NULL
677876BRAND_AWARENESS_Q12026-01-10 09:03:000NULL
730973PRODUCT_LAUNCH_X2026-02-11 10:10:0010.35

Expected output

First 3 of 3 rows
join_typerow_count
inner_join180
left_join355
full_outer_join375

Worked solution and explanation

Why this problem exists in real interviews

Same two tables, three different join semantics, three different row counts. The interviewer is checking that you can name what each join keeps and drops, and that you can emulate FULL OUTER on engines that don't support it (SQLite, MySQL pre-8) by stitching two LEFT JOINs together with UNION ALL.

Break down the requirements

Step 1: One row per join type, three branches

Each join type produces one row in the output: 'inner_join', 'left_join', 'full_outer_join'. Stitch them with UNION ALL so all three counts appear in a single result set with two columns: join_type, row_count.

Step 2: Inner and left are direct

Inner: INNER JOIN keeps only matched pairs. Left: LEFT JOIN keeps every user, with one row per matched impression and exactly one row (with NULLs) for unmatched users.

Step 3: Emulate full outer with two LEFT JOINs

Full outer: SQLite has no FULL OUTER JOIN, so build it as the LEFT JOIN result UNION ALL the right-only rows. The right-only branch is ad_impressions LEFT JOIN users with WHERE u.user_id IS NULL, which keeps the impressions that had no matching user.

The solution

Three counts side by side, FULL OUTER emulated for SQLite
SELECT 'inner_join' AS join_type, COUNT(*) AS row_count
FROM users u
INNER JOIN ad_impressions ai ON u.user_id = ai.user_id
UNION ALL
SELECT 'left_join', COUNT(*)
FROM users u
LEFT JOIN ad_impressions ai ON u.user_id = ai.user_id
UNION ALL
SELECT 'full_outer_join', COUNT(*)
FROM (
    SELECT u.user_id, ai.impression_id
    FROM users u
    LEFT JOIN ad_impressions ai ON u.user_id = ai.user_id
    UNION ALL
    SELECT u.user_id, ai.impression_id
    FROM ad_impressions ai
    LEFT JOIN users u ON ai.user_id = u.user_id
    WHERE u.user_id IS NULL
)

Common follow-up questions