Who Comes Early

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

Every year opens with a wave of new names. See how the first seven months compare.

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

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.

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

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