Welcome Wagon

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
Asked in
City and County of San Francisco

Problem

For the growth team's annual review, how many accounts in the users table signed up during calendar year 2025?

How many signed up this year?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 1 of 1 rows
signup_count
59

Worked solution and explanation

What this warm-up is really checking

This is a two-sided date window dressed up as a headcount. Anyone can write COUNT(*). What separates candidates is whether the year is fenced on both ends. Last year sits in the middle of the data: there are signups before it and signups after it in 2026. Write only signup_date >= '2025-01-01' and every signup from this year joins last year's total, so the annual review reports growth that never happened.

Step 1: Open the window on January 1

signup_date >= '2025-01-01' drops every signup from 2023 and 2024. On its own it is not a year filter. It means 'since last year began', and that includes today.

Step 2: Close it on the next January 1, exclusive

signup_date < '2026-01-01' cuts off this year's signups. Use a strict less-than against the first day of the next year, not <= '2025-12-31'. Some signup_date values already carry a time of day, and a signup at 18:00 on December 31 sorts after the bare date string, so the inclusive form would lose it.

Step 3: Count what survives

COUNT(*) over the filtered rows is the answer. A timestamped value such as '2025-08-08 16:34:00' still sorts between the two text bounds, so it lands in its year with no conversion and no special case.

Half-open calendar-year window
SELECT COUNT(*) AS signup_count
FROM users
WHERE signup_date >= '2025-01-01'
  AND signup_date < '2026-01-01'
One-sided filter
WHERE signup_date >= '2025-01-01' reads like 'last year' but counts last year plus everything in 2026 so far.
Two-sided filter
signup_date >= '2025-01-01' AND signup_date < '2026-01-01' counts exactly one calendar year, no matter how much data sits on either side.

Common follow-up questions