The First Half

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
Maana

Problem

How many users signed up from January 1 through July 31 of 2026, both days included?

New arrivals during one specific window.

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
42

Worked solution and explanation

The edges are the whole question

This is a closed date window dressed up as cohort sizing, and the only real decision is where its two edges sit. Anyone can write COUNT(*). What separates candidates is noticing that signup_date is TEXT and that the sample rows show it holding both plain dates and timestamps like '2025-08-08 16:34:00'. That means BETWEEN '2026-01-01' AND '2026-07-31' reads 'July 31 inclusive' as July 31 at midnight. On today's seed no July 31 signup carries a time, so that version also returns 42. The edges that bite here are the other two: drop the January 1 edge and earlier years flood in, drop the July 31 edge and the 16 August and September signups push the count to 58.

Build the window

Step 1: Open the window on January 1

Start at signup_date >= '2026-01-01'. The table holds signups back to 2023, so a missing lower edge quietly turns a cohort count into an all-time count.

Step 2: Close it at the first instant of August

'Through July 31' means everything before August 1, so write signup_date < '2026-08-01'. ISO strings compare in calendar order, so '2026-07-31 18:05:00' sorts below '2026-08-01' and stays in. Leave this edge off and the August and September 2026 signups take the count from 42 to 58.

Step 3: Count rows, filter nothing else

Each row of users is one account and user_id is unique, so COUNT(*) is the cohort size, returned as signup_count. Add no account_status predicate: the window is about when people joined, not what they are now.

Early-year cohort count
SELECT COUNT(*) AS signup_count
FROM users
WHERE signup_date >= '2026-01-01'
  AND signup_date <  '2026-08-01'
Closed at July 31
signup_date <= '2026-07-31' (or BETWEEN) compares the text against midnight of July 31, so '2026-07-31 18:05:00' is greater than the bound and falls out.
Open at August 1
signup_date < '2026-08-01' keeps every July 31 value, with or without a time, and shuts out August. It returns the same 42 on today's seed, but it is the only version that stays right when a late-July signup lands with a timestamp.

Common follow-up questions