Filtered User Roster

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

Amazon

Problem

The growth team is assembling an outreach roster for the all-hands and only wants reachable users, so leave out any account that is suspended or still pending verification, plus the test accounts flagged by a 'z' somewhere in the email. Keep everyone else, including users who have not added an email yet, and list each full profile alphabetically by username.

A clean roster for the all-hands.

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

It looks like a warm-up. The nulls are the whole game.

This is a plain row filter wearing a marketing costume, and that is exactly why people relax and lose it. Two exclusions look obvious: drop the accounts you cannot reach, and drop the test accounts whose email carries a 'z'. The skill actually being probed is whether you remember what NOT LIKE does to a NULL email. It does not return FALSE. It returns NULL, which is not TRUE, so the row gets thrown out. Get that wrong and every user who has not added an email yet vanishes from a roster that was supposed to include them.

Why NOT LIKE quietly eats the missing-email rows

SQL uses three-valued logic. For a row where email IS NULL, the predicate email NOT LIKE '%z%' evaluates to NULL, not TRUE. The WHERE clause only keeps rows where the whole condition is TRUE, so those rows are filtered out even though they contain no 'z' at all. Roughly 5 percent of users have no email, and that is exactly the population the parenthesized OR branch exists to rescue.

Drops missing-email users (wrong)

WHERE email NOT LIKE '%z%' returns NULL for every NULL email, so those users silently disappear. The result undercounts and nobody sees an error.

Keeps them (correct)

WHERE (email NOT LIKE '%z%' OR email IS NULL) restores the rows whose email is absent, and excludes only emails that are present AND contain a 'z'.

The solution

SELECT *
FROM users
WHERE account_status NOT IN ('suspended', 'pending_verification')
  AND (email NOT LIKE '%z%' OR email IS NULL)
ORDER BY username

Step 1: Drop the unreachable accounts

Drop the unreachable accounts with account_status NOT IN ('suspended', 'pending_verification'). NOT IN is safe here because account_status is never NULL. If it could be, that NULL would make NOT IN return NULL and surprise you the same way a NULL email does below.

Step 2: Filter emails without losing the nulls

Filter out emails containing a 'z' with email NOT LIKE '%z%', then OR in email IS NULL so users with no email survive. Keep that pair inside parentheses: AND binds tighter than OR, and without them the null branch lets suspended and pending users with no email back in.

Step 3: Return the full row, sorted

Order alphabetically by username and return every column with SELECT *, since the ask is the full profile, not a projection.

Common follow-up questions

• If the requirement flipped to keeping only emails that contain a 'z', would `NULL` emails be included or excluded, and what would you write? (Tests deeper command of three-valued logic on the inclusion side.)
• If some emails store the marker as an uppercase 'Z', how do you make the exclusion catch both without breaking on engines where `LIKE` is case-sensitive? (Tests case-sensitivity awareness in pattern matching across engines.)
• If `username` values were not guaranteed unique, how would you make the row order fully deterministic? (Tests determinism of ordering on a possibly non-unique sort key.)