Email Census

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

Every row in users carries an account_status, and its email is NULL when no address is on file. Break the user base down by status and by whether an email exists, giving each combination's user count and its share of all users as a percentage rounded to one decimal place.

The reachability split.

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 8 rows
account_statusemail_statususer_countpct
activehas email4623
activeno email42
inactivehas email4723.5
pending_verificationhas email4522.5
suspendedhas email4623

Worked solution and explanation

What this really is

This is a percent-of-grand-total problem dressed up as a reachability census. Anyone can write the CASE that labels each account and the GROUP BY that counts it. What separates candidates is the denominator. Inside a grouped query each row sees only its own bucket, never the full user base, so the total has to come from outside the group. The second trap is quieter: write * 100 / and integer division truncates every share, and ROUND has nothing left to round.

Step 1: Label every account in place

CASE WHEN email IS NULL THEN 'no email' ELSE 'has email' END turns the missing-address property into a value you can group on, right in the SELECT. Only real absence is NULL. An empty string is still an address on file, and it belongs in 'has email'.

Step 2: Group on the status and the label expression

Put account_status and the same CASE expression in the GROUP BY. No intermediate result is needed: the engine evaluates the label once per row and buckets on it. Every status splits into its reachable and unreachable accounts, and COUNT(*) sizes each piece.

Step 3: Divide by the whole base, not the status

(SELECT COUNT(*) FROM users) is a scalar subquery that runs once and gives every row the same 5,000,000 denominator. Multiply by 100.0 first so the division stays fractional, then round to one decimal place.

Status by reachability, as shares of all users
SELECT
  account_status,
  CASE WHEN email IS NULL THEN 'no email' ELSE 'has email' END AS email_status,
  COUNT(*) AS user_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM users), 1) AS pct
FROM users
GROUP BY
  account_status,
  CASE WHEN email IS NULL THEN 'no email' ELSE 'has email' END
Integer math
COUNT(*) * 100 / (SELECT COUNT(*) FROM users) keeps only the whole number in engines that divide integers exactly. A 2.4% segment reports 2, a 0.3% segment reports 0, and ROUND(..., 1) is rounding a value that was already truncated.
Fractional math
COUNT(*) * 100.0 / ... promotes the arithmetic to decimal before dividing, so 2.4 survives and ROUND does real work.

Common follow-up questions