# February 2024 Signups

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

Canonical URL: <https://datadriven.io/problems/february_year_2_signups>

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.

## Tables

### users

- user_id INTEGER
- username TEXT
- email TEXT
- signup_date TEXT
- account_status TEXT
- age_bucket TEXT

Sample rows (10 of 200):

| 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 5 of 9 rows:

| user_id | username | email | signup_date | account_status | age_bucket |
| --- | --- | --- | --- | --- | --- |
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 1264 | bilal | bilal@example.com | 2025-02-14 | inactive | NULL |
| 2428 | celine | celine@example.com | 2025-02-26 | inactive | 55-64 |
| 3592 | evelyn | evelyn@example.com | 2025-02-10 | inactive | 35-44 |
| 4756 | gunnar | gunnar@example.com | 2025-02-22 | inactive | 18-24 |

## 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.

**The month-only version**

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

*Matches February of every year, so other years' signups join the cohort.*

#### 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.

**Canonical solution**

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

*One equality on a derived year-month label: year and month pinned together, no month-length arithmetic.*

> **Name the year-month, do not bracket the days**
>
> Collapsing the date to a '%Y-%m' label and testing equality kills two bugs at once: the wrong-year leak of a month-only match and the boundary guessing of a hand-typed range.

> **A prefix match is a coincidence**
>
> `signup_date LIKE '2025-02%'` returns the right rows here only because the dates are stored as ISO text. It breaks the moment the column becomes a real `DATE` or arrives in another format.

**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.

> **A wrapped column cannot use the index**
>
> Wrapping `signup_date` in a function hides it from a plain index, so the engine scans. On a small `users` table that is irrelevant. At tens of millions of rows, write the half-open range `signup_date >= '2025-02-01' AND signup_date < '2025-03-01'`: index-friendly, and the upper bound is the start of the next month, never a guessed last day.

> **Boundary reasoning nobody asked for**
>
> On a question this small, a correct answer is table stakes. What gets remembered is saying unprompted that the year must be pinned, and naming the trade-off between the label and a half-open range.

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/february_year_2_signups)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.