No Gaps

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

Problem

Each row of users is one account with a username, an email, an age bucket and an account status, and the email or age bucket is sometimes missing. A campaign blast needs every active account as a mail-merge contact with no blank fields, so a missing email reads 'unknown' and a missing age bucket reads 'unspecified'.

Zero blanks. A clean contact list.

Tables

users

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

Expected output

Worked solution and explanation

This is a fill-not-filter question dressed up as a campaign export. Ten of the 50 active accounts on this seed are missing an email or an age bucket, and 'no blank fields' tempts half the room into WHERE email IS NOT NULL AND age_bucket IS NOT NULL: a spotless list of 40 people and a quietly smaller campaign. The other half projects the raw columns and ships blanks to the merge. The skill is seeing that a blank is a cell problem, not a row problem: the row stays, the cell gets a default.

Walking to the answer

Step 1: Scope to the exact 'active' status

account_status holds 'active', 'inactive', 'suspended' and 'pending_verification'. Only the first is a recipient, so the filter is an equality on 'active'. That WHERE is the only clause allowed to remove rows; nothing about missing data should touch the row count.

Step 2: Fill each blank cell with its own placeholder

COALESCE(email, 'unknown') returns the email when it exists and 'unknown' when it is NULL; COALESCE(age_bucket, 'unspecified') does the same for the bucket. One COALESCE per column, because each column has its own placeholder and the blanks are independent: on this seed no active account is missing both.

Step 3: Alias the filled columns back to email and age_bucket

The merge template binds by column name. A result column that comes out named after the expression breaks the template without a single wrong value in it, so name the filled columns exactly what the raw ones were called.

SELECT
    username,
    COALESCE(email, 'unknown') AS email,
    COALESCE(age_bucket, 'unspecified') AS age_bucket
FROM users
WHERE account_status = 'active'

SELECT
    username,
    email,
    age_bucket
FROM users
WHERE account_status = 'active'
  AND email IS NOT NULL
  AND age_bucket IS NOT NULL

Filter the blanks out

email IS NOT NULL AND age_bucket IS NOT NULL makes the output look clean by shrinking it: 40 rows, and nobody downstream can tell ten recipients were dropped.

Fill the blanks in

COALESCE per column keeps all 50 rows and makes every missing value visible as a known placeholder the sender can act on.

Common follow-up questions

• What changes if a missing email sometimes arrives as an empty string instead of `NULL`? (Tests whether the candidate knows `COALESCE` only sees `NULL` and turns '' into `NULL` first, for example with `NULLIF(email, '')`.)
• One active username, 'arjun ', carries a trailing space. Do you clean it, and where? (Tests whether the candidate spots padding in the preview and argues for `TRIM` in the export versus fixing it at ingestion.)
• Marketing also wants to know how many contacts received each placeholder. How do you produce that alongside the list? (Tests conditional counting over the same filtered set, such as `COUNT(*) FILTER (WHERE email IS NULL)`, without a second pass.)