Everyone Counts

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
Philips

Problem

The growth team is profiling last year's signups that are still waiting to verify their accounts, broken out by age band. Plenty of those accounts have no band on file, and the team wants them kept as a segment of their own rather than dropped. Show the bands that pulled in more than four of these signups along with how many landed in each.

Every account lands in a segment, even the ones with nothing on file.

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 4 of 4 rows
age_bucketuser_count
NULL5
18-246
45-545
55-645

Worked solution and explanation

The reflex this problem is really testing

Underneath the demographics story, this is a grouped head count with a cutoff no single row can see, plus a segment everyone forgets to keep. You scope to last year's unverified signups with WHERE account_status = 'pending_verification' AND signup_date LIKE '2025-%'. 'More than four signups in the band' is a fact about a band, though, and a band's size only exists after GROUP BY age_bucket, so that test sits in HAVING. The real trap is the accounts with no age on file: they are a band too. Reach for WHERE age_bucket IS NOT NULL on autopilot, or count with COUNT(age_bucket), and the no-band segment silently disappears even though it clears the bar with five signups. Four rows become three, and the one you lost is the one the growth team asked you to keep.

Break down the requirements

Step 1: Scope to last year's pending signups, then group

WHERE account_status = 'pending_verification' AND signup_date LIKE '2025-%' keeps only last year's still-unverified signups. The prefix match also catches the odd signup_date stored with a time of day. GROUP BY age_bucket then collapses these signups into one row per band. Every account with no age on file falls into a single null group, and that group is exactly the no-band segment the team asked to keep, so there is no filter on age_bucket at all.

Step 2: Filter bands by size after grouping

The cutoff is a per-band count, which only exists once the bands exist, so it lives in HAVING COUNT(*) > 4. A row-level WHERE runs before grouping and has no band size to test. The cutoff drops the three thin bands sitting at four ('25-34', '35-44', '65+') and keeps the bands that actually drew a crowd.

Step 3: Count rows, not labels

COUNT(*) counts every account in the group, the null group's included. COUNT(age_bucket) counts only non-null labels, so the no-band group would report zero and fail the cutoff with five accounts sitting in it. The metric is a head count, so count rows.

The solution

Scope, group, and keep the bands above the cutoff
SELECT age_bucket, COUNT(*) AS user_count
FROM users
WHERE account_status = 'pending_verification' AND signup_date LIKE '2025-%'
GROUP BY age_bucket
HAVING COUNT(*) > 4

Common follow-up questions