Missing Email for Non-Active Users

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
City and County of San Francisco

Problem

Which non-active accounts in users are too incomplete for a win-back campaign, with no email to send to (never filled in or saved blank) or no age bracket to segment by? The lifecycle marketer building the list wants each such user with the year they signed up, whatever their specific non-active status.

No email on file. No recent activity. Something smells off.

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

Expected output

First 5 of 32 rows
user_idsignup_year
5852024
10702026
12642025
26222024
113522023

Worked solution and explanation

Three ways to be incomplete

This is a 'what counts as missing' question dressed up as a marketing list. Pulling a year off a date and excluding active accounts is the easy part. What separates candidates is that an incomplete profile hides three ways in users: an email that is NULL, an email saved as '', and an age_bucket that is NULL. Miss one and the list still looks plausible; drop the parentheses and the status filter only guards the last check, so active users ride along. Either way the campaign targets the wrong people.

Building the list

Step 1: Catch both storage forms of a missing email

email = '' never matches a NULL row, because that comparison is never true and a WHERE keeps only true rows. email IS NULL never matches a blank. You need both, or a form that folds them together like COALESCE(email, '') = ''.

Step 2: Add the missing age bracket

An account with an address but no age_bucket still cannot be segmented, so age_bucket IS NULL is a third alternative in the same condition.

Step 3: Group, then exclude active accounts

Wrap the three alternatives in one set of parentheses, then add account_status <> 'active'. That keeps 'inactive', 'suspended' and 'pending_verification' alike, and the negation applies to every alternative.

Step 4: Extract the year from signup_date

signup_date is text, so STRFTIME('%Y', signup_date) returns the four-digit year the marketer segments the list by.

Every incomplete profile, every non-active status
SELECT
    user_id,
    STRFTIME('%Y', signup_date) AS signup_year
FROM users
WHERE (email IS NULL OR email = '' OR age_bucket IS NULL)
    AND account_status <> 'active'
No parentheses
email IS NULL OR email = '' OR age_bucket IS NULL AND account_status <> 'active' reads as three separate alternatives, with the status test glued to the last. Active users with no email come back.
Grouped condition
(email IS NULL OR email = '' OR age_bucket IS NULL) AND account_status <> 'active' applies the status test to every alternative. Only lapsed accounts remain.

Common follow-up questions