# Who Comes Early > Every year opens with a wave of new names. See how the first seven months compare. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem We record each user's signup date and want to compare the year-opening intake from one year to the next. Count the users who signed up between January and July, earliest year first. ## Worked solution and explanation ### What this problem actually is Strip the reporting language away and this is a month-of-year filter feeding a per-year count. You are not asked for one year's January-to-July total, you are asked to line those totals up across every year in the table. The whole problem lives in one decision: how you express the January-through-July window. Get that decision wrong and you either collapse the answer down to a single year or quietly let August slip in. ### The mistake everyone makes first The reflex is to reach for a date range on signup_date. That reflex is exactly the trap here, because a date range pins you to one specific year and there is no year in the prompt to pin to. The requirement is a recurring seasonal window, the first seven calendar months, evaluated in every year at once. **Fixed date range (wrong)** WHERE signup_date BETWEEN '2024-01-01' AND '2024-07-31'. This hardcodes 2024, so 2025 and 2026 vanish and the cross-year comparison the question is built around disappears. It also leans on getting the upper-bound string exactly right. **Month-of-year filter (right)** WHERE the month extracted from signup_date is 1 through 7. This keeps the January-to-July rows from every year, so grouping by year hands you one comparable count per year. #### Step 1: Pull the month out of the date In this SQLite sandbox there is no EXTRACT, so use strftime('%m', signup_date) to get a zero-padded month string, then cast it to an integer so you can compare it numerically. This turns a full date into just the seasonal coordinate you care about. #### Step 2: Keep January through July BETWEEN 1 AND 7 is inclusive on both ends, so it captures all of July without you having to reason about July 31 versus August 1. Because you filtered on the month alone, the year is left free to vary, which is precisely what lets the same window repeat across years. #### Step 3: Count within each year Pull the year out with strftime('%Y', signup_date), group by it, and COUNT(*) each group. Ordering by the year string ascending gives the earliest-year-first sequence the prompt asks for, and because years are zero-free four-digit strings the lexical order matches the numeric order. **Per-year January-to-July signup counts** ```sql SELECT strftime('%Y', signup_date) AS signup_year, COUNT(*) AS signup_count FROM users WHERE CAST(strftime('%m', signup_date) AS INTEGER) BETWEEN 1 AND 7 GROUP BY signup_year ORDER BY signup_year ``` *One filtered aggregation: a month-of-year predicate feeding a grouped count.* > **The July boundary** > > If you do insist on date strings, the safe upper bound is a half-open '< 2024-08-01', never '<= 2024-07-31', which silently drops any timestamp later in the day on July 31. Filtering on the month number sidesteps the whole class of off-by-one boundary bugs. > **What the interviewer is checking** > > They want to see whether you notice there is no year in the prompt. A candidate who writes a fixed 2024 range has misread the ask; a candidate who filters on month-of-year and groups by year has understood that the window is seasonal and recurring. That read is the entire signal. > **Why this stays cheap** > > It is a single sequential scan with a grouped aggregate, no join and no subquery. The derived month expression is not sargable, so an index on signup_date will not be used for the filter, but on a users table this is a full scan you were going to pay for anyway. At real scale you would precompute a signup_year and signup_month column and index those. ## Common follow-up questions - How would you show zero for a year that had no January-to-July signups at all? _(Tests whether they know a plain filter cannot invent absent groups, and that a calendar or numbers table left-joined in is the fix.)_ - Now break each year's count down by month as well, January through July. _(Pushes toward grouping on both the extracted year and the extracted month.)_ - How does the query change on Postgres instead of SQLite? _(Checks portability awareness: EXTRACT(MONTH FROM signup_date) or date_part replaces strftime.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/who_comes_early) - [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.