April and May Active Users

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

ActiveCampaign

Problem

Return the user IDs with at least one session in April or May, each user listed once, lowest ID first. Read them from user_sessions, where a spring month counts in every year the log covers, not only the current one.

Spring cleaning for the user base. Who was actually around?

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

This is a dedupe of a non-unique key wearing a growth-team costume. user_id is not a key in user_sessions: one person logs several sessions, and more than one of them can land in April or May. Anyone writes the month filter. What separates candidates is noticing that the filter returns sessions, not people. Skip the collapse and the user list repeats the same ID once per qualifying session, so every headcount and every campaign built on it fires more than once per person.

Read the grain before you filter

Step 1: Pull a numeric month out of session_start

session_start is text like '2026-04-04 03:00:00'. Extract its month and turn it into a number before comparing it to 4 and 5.

Step 2: Match the month, not one year's dates

Spring here means April and May in every year the log holds, and user_sessions spans 2023 through 2026. A single BETWEEN on one year's dates quietly drops the earlier springs and the users who were only active then.

Step 3: Collapse sessions into people

Project user_id alone and put DISTINCT on it. Selecting session_id or session_start next to it would make every row unique again and undo the collapse.

Step 4: Sort lowest ID first

ORDER BY user_id gives the order the list is read in. After the collapse every ID is unique, so there are no ties to settle.

The solution

SELECT DISTINCT user_id
FROM user_sessions
WHERE CAST(strftime('%m', session_start) AS INTEGER) IN (4, 5)
ORDER BY user_id

One row per session

SELECT user_id with the month filter and no DISTINCT: a user with three spring sessions shows up three times.

One row per user

SELECT DISTINCT user_id: one row per person, whatever their session count. GROUP BY user_id returns the same set, but DISTINCT states the intent with less.

Common follow-up questions

• How many unique users were active each spring, one row per year? (Moves the year into the output grain, so the dedupe happens per year, not overall.)
• Return only the users active in both April and May of the same year. (Turns a membership filter into a per-user condition across two months.)
• How would you write the filter so a date-partitioned `user_sessions` can prune partitions? (Tests whether the candidate knows a function on the partition column defeats pruning.)
• What changes if `session_start` is stored in UTC and spring should follow each user's local time? (Probes converting time zones before extracting the month.)