This Year's Class

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

We're putting the 2025 signup class into our year-end growth report. Count how many users joined that year.

The cohort is in. Time to count who made it through the door.

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_id	username	email	signup_date	account_status	age_bucket
100	alice	alice@example.com	2025-02-02	inactive	25-34
197	aaron42	aaron42@example.com	2026-03-03	suspended	35-44
294	amelia	amelia@example.com	2024-04-04	pending_verification	45-54
391	arjun	arjun@example.com	2025-05-05	active	55-64
488	ava99	ava99@example.com	2026-06-06	inactive	65+
585	andrew_k	andrew_k@example.com	2024-07-07	suspended	NULL
682	anika	anika@example.com	2025-08-08 16:34:00	pending_verification	18-24
779	aiden	aiden@example.com	2026-09-09 14:53:00	active	25-34
876	aria_b	aria_b@example.com	2024-10-10	inactive	35-44
973	brian	brian@example.com	2025-11-11	suspended	45-54

Expected output

First 1 of 1 rows
signup_count
59

Worked solution and explanation

What is really being asked

Strip off the year-end-report costume and this is one number: how many rows of users fall in calendar year 2025. The COUNT(*) is free. What separates candidates is the year test, because signup_date is TEXT and not every value has the same shape. Most read like '2025-11-11', but some carry a time of day, like '2025-08-08 16:34:00'. Assume every value is a bare date and that signup falls out of the class. Compare the year as a number and the count drops to zero. The answer is 59, and either mistake ships a confident wrong number into the report.

The count for the 2025 class

SELECT COUNT(*) AS signup_count
FROM users
WHERE strftime('%Y', signup_date) = '2025';

Extract the year from each signup_date, keep only 2025, then count the survivors.

Step 1: Reduce each date to its year

strftime('%Y', signup_date) hands back '2025' for both '2025-11-11' and '2025-08-08 16:34:00'. The time of day never enters the test, so every row is judged by its year alone, which is exactly the grain the report cares about.

Step 2: Compare against a string, not a number

strftime returns text, so the test is = '2025' with quotes. Writing = 2025 compares text to a number, which never matches, and the count silently comes back 0. If you would rather work in numbers, EXTRACT(YEAR FROM signup_date) = 2025 returns an integer and is graded the same.

Step 3: Count everyone who survives

COUNT(*) over the filtered rows is the whole class. Add no account_status filter: suspended, inactive and pending accounts all joined that year, and keeping only today's active ones shrinks the class to 9. Alias the count AS signup_count so the report column has a real name.

Assumes every value is a bare date: WHERE signup_date LIKE '2025-__-__' matches exactly ten characters, so '2025-08-08 16:34:00' fails the pattern and the count comes back 58.
Reads only the year: WHERE strftime('%Y', signup_date) = '2025' ignores whatever follows the date, so the timestamped signup stays in and the count is 59.

Common follow-up questions

Now break the same count down by year instead of one fixed year. (Tests moving the extracted year from the `WHERE` clause into `GROUP BY` and the `SELECT` list.)
Count only signups whose `account_status` is 'active' in that year. (Adds a second predicate and checks the two filters combine cleanly.)
Rewrite it so an index on `signup_date` can be used. (Probes the half-open range rewrite from the performance note, and whether it still keeps timestamped rows.)