Even-ID June Signups

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
Bosch

Problem

The growth team is investigating a signup anomaly affecting June cohorts. Pull the full user profile for every June signup whose user ID is an even number, working up from the lowest user ID to the highest.

Odd IDs, even IDs. The filter is precise.

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 5 of 6 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
488ava99ava99@example.com2026-06-06inactive65+
2234cara_zcara_z@example.com2026-06-24pending_verification35-44
3980fionafiona@example.com2026-06-14inactiveNULL
5726jackjack@example.com2026-06-04pending_verification45-54
7472leo99NULL2026-06-22inactive18-24

Worked solution and explanation

This is a two-condition row filter in a growth-investigation costume, and the only part that can really go wrong is how you read the month. signup_date is text, and 'June' means the month position of that text, whatever the year. Everyone writes the parity test. The candidates who slip match '06' anywhere in the string. LIKE '%06%' also catches the 6th of October and the 6th of September, and on this table four even-ID users dated the 6th of some other month end up in a June cohort that should hold six.

Read the requirements off the words

Step 1: Pull the month out of signup_date

Dates are stored as 'YYYY-MM-DD', and a couple of rows have a time of day appended. strftime('%m', signup_date) returns the two-character month, so comparing it to '06' keeps every June in every year and ignores the day and the time. The comparison is text to text: '06', not 6.

Step 2: Test the ID value, not the row position

user_id % 2 = 0 checks the ID itself. IDs here step by 97, so they alternate odd and even in a way that has nothing to do with where a row sits; numbering rows and keeping every second one picks a different set.

Step 3: Sort by user_id ascending

The ask is to work up from the lowest ID, so ORDER BY user_id. The ID is unique, so there is no tie to settle.

Month of the text date plus an ID parity test
SELECT
  user_id,
  username,
  email,
  signup_date,
  account_status,
  age_bucket
FROM users
WHERE strftime('%m', signup_date) = '06'
  AND user_id % 2 = 0
ORDER BY user_id
A substring match
signup_date LIKE '%06%' matches '06' wherever it sits, so the 6th of any month qualifies. On this table that adds four even-ID users dated the 6th of October, February or September: ten rows instead of six.
The month position
strftime('%m', signup_date) = '06' reads only the month. The day and a trailing time cannot leak in, and every year's June is kept.

Common follow-up questions