# February 2024 Signups > One signup window. One cohort. Who joined the club? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The lifecycle marketing team is rebuilding a campaign for users who joined in February of 2024. Pull every column for every user whose signup falls in that month. ## Worked solution and explanation Strip the marketing costume off and this is a single-month date filter. The whole problem lives in one decision: how do you say 'this row's signup_date is in February of a specific year' in SQL? Everyone writes the SELECT and the WHERE. The thing that separates a clean answer from a buggy one is whether your month boundary is exact. Get it slightly wrong and you either leak January 31 / March 1 neighbors into the cohort or you drop legitimate February signups, and the marketing team mails the wrong list. ### The trap: comparing a date as if it were a number The reflex on an easy date question is BETWEEN. People write WHERE signup_date BETWEEN '2025-02-01' AND '2025-02-28'. That looks right until you remember BETWEEN is inclusive on both ends and that you have to know the last day of the month. In a leap year February has 29 days, so '2025-02-28' silently amputates the 29th. And the moment any column stores a time component, BETWEEN against a bare date misses everything after midnight on the upper bound. The fix is to stop hardcoding the month's last day at all: derive the year-and-month label from each row and compare labels. **The fragile version** ```sql SELECT * FROM users WHERE signup_date BETWEEN '2025-02-01' AND '2025-02-28'; ``` *Inclusive upper bound + hardcoded last day. Drops Feb 29 in leap years and any timestamped rows past midnight on the 28th.* #### Step 1: Reduce each signup_date to a year-month label strftime('%Y-%m', signup_date) turns '2025-02-14' into the string '2025-02'. It does not care how many days the month has, so leap years and time components are no longer your problem. Every row in the same calendar month collapses to the same label. #### Step 2: Compare the label to the target month WHERE strftime('%Y-%m', signup_date) = '2025-02' keeps exactly the rows whose signup landed in February of that year. One equality check replaces the two-sided range and the off-by-one risk that comes with it. #### Step 3: Return every column The campaign team wants the full record to build the audience, so SELECT * is the right call here, not a hand-picked column list. The prompt asks for every column; honoring that literally is correct rather than over-engineering a projection. **Canonical solution** ```sql SELECT * FROM users WHERE strftime('%Y-%m', signup_date) = '2025-02'; ``` *One equality on a derived year-month label. No month-length arithmetic, leap-year safe.* > **Trick to solving** > > Match the month, do not bracket the days. Collapsing the date to a '%Y-%m' label and testing equality sidesteps every boundary bug that range queries invite: leap days, the last-day-of-month question, and stray time components. > **Common pitfall** > > WHERE signup_date LIKE '2025-02%' happens to work in SQLite because dates are stored as ISO text, but it is brittle: it breaks the instant the column is a real DATE/DATETIME type or stored in any other format, and it cannot use an index on a typed column. Treat it as a coincidence, not a technique. **Range on raw date (fragile)** BETWEEN '2025-02-01' AND '2025-02-28' forces you to know the last day, is inclusive on both ends, drops Feb 29 in leap years, and misses timestamped rows after midnight on the 28th. **Equality on year-month label (robust)** strftime('%Y-%m', signup_date) = '2025-02' needs no month-length knowledge, has a single clean boundary, and is identical for leap and non-leap years. > **Performance insight** > > Wrapping the column in strftime() makes the predicate non-sargable: the engine cannot use a plain index on signup_date and will scan. For a small users table that is irrelevant. At tens of millions of rows you would instead bound a half-open range, signup_date >= '2025-02-01' AND signup_date < '2025-03-01', which is index-friendly AND leap-safe because the upper bound is the exclusive start of the next month, never a guessed last day. > **Interviewers watch for** > > The tell of seniority on a question this small is unprompted boundary reasoning: mentioning leap years, the inclusive-vs-exclusive bound, and the index trade-off between strftime and a half-open range. Producing a correct answer is table stakes; naming why the obvious BETWEEN is risky is what gets remembered. ## 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 strftime is non-sargable and can produce the half-open range signup_date >= '2025-02-01' AND signup_date < '2025-03-01'.)_ - The team now wants every February across all years, not just one. How does the predicate change? _(Tests month extraction independent of year: strftime('%m', signup_date) = '02'.)_ - signup_date is sometimes NULL. What happens to those rows and is that the behavior you want? _(Tests understanding that NULL fails the equality and is silently excluded, which here is correct but should be stated.)_ ## 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.