February 2024 Signups

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

February signups from other years don't count: the lifecycle campaign being rebuilt targets only the users who joined in February 2025. Pull every column from users for each of them, whatever their account status.

One signup window. One cohort. Who joined the club?

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

Strip the marketing costume off and this is a filter for one month of one specific year. The whole problem is a single decision: how do you say that a row's signup_date sits in February 2025, not just in some February? Everyone writes the SELECT and the WHERE. What separates a clean answer from a buggy one is whether the year and the month are pinned together. Match the month alone and the cohort swallows every other year's February; bracket the days by hand and you risk the edge of the month. Either way the campaign mails the wrong list.

The trap: February is not a month, it is a year-month

The first reflex is strftime('%m', signup_date) = '02'. It reads like the question, and on this seed it is wrong: users holds February signups from other years too, and every one of them slips in. The second reflex is a hand-typed range, BETWEEN '2025-02-01' AND '2025-02-28'. That one makes you know the last day of the month, is inclusive on both ends, and quietly misses anything after midnight on the 28th once a time component appears. Stop guessing boundaries: derive a year-month label from each row and compare labels.

SELECT *
FROM users
WHERE strftime('%m', signup_date) = '02';

Step 1: Reduce each signup_date to a year-month label

strftime('%Y-%m', signup_date) turns '2025-02-14' into '2025-02'. The year rides along with the month, so another year's February produces a different label, and month length never enters the picture.

Step 2: Compare the label to the target month

One equality, = '2025-02', keeps exactly the rows whose signup landed in that month of that year. It replaces both the month-only match and the two-sided range, along with the off-by-one risk the range carries.

Step 3: Return every column, every status

The campaign needs the full record, and no status filter applies: a suspended account that joined that month is still part of the cohort. SELECT * with nothing but the date predicate is the literal answer.

SELECT *
FROM users
WHERE strftime('%Y-%m', signup_date) = '2025-02';

Month only, or a guessed range

strftime('%m', signup_date) = '02' matches every year's February; BETWEEN '2025-02-01' AND '2025-02-28' makes you guess the last day and misses timestamped rows on it.

Equality on a year-month label

strftime('%Y-%m', signup_date) = '2025-02' pins the year, needs no month length, and has one clean boundary.

Common follow-up questions

• Rewrite this so it stays correct and can use an index on `signup_date` at large scale. (Tests whether they know a wrapped column defeats an index and can write the half-open range on `signup_date`.)
• The team now wants every February across all years. How does the predicate change? (Tests month extraction without the year: `strftime('%m', signup_date) = '02'`, the very predicate that was wrong here.)
• `signup_date` is sometimes `NULL`. What happens to those rows, and is that what you want? (A `NULL` date fails the equality and is silently excluded; correct here, but it should be said out loud.)