Session Logins Dec 13 to 19
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
- Sears
Problem
Which unique users started a session from June 13 through December 19, 2025, the stretch the security team has flagged for suspicious logins in user_sessions?
Logins during one specific window.
Tables
user_sessions
Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER
| session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed |
|---|---|---|---|---|---|
| 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 |
| 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 |
| 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 |
| 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 |
| 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 |
| 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 |
| 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 |
| 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 |
| 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 |
| 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 |
Expected output
| user_id |
|---|
| 6114 |
| 6502 |
| 7278 |
| 7666 |
| 8442 |
Worked solution and explanation
What this really is
This is a set-membership question dressed up as a security review: who appears at least once inside a box of whole days. Everyone writes the filter and the dedupe. What separates candidates is the last day. session_start is a timestamp, not a date, so BETWEEN '...-06-13' AND '...-12-19' stops at midnight on the 19th and quietly drops almost every session that started that day. The investigation then misses exactly the users whose only activity was on the final day of the stretch.
Step 1: Bound the window to whole days
The lower bound '...-06-13' is midnight, which is exactly where the 13th begins, so it is right as written. The upper bound has to be the end of the 19th, so it becomes '...-12-19 23:59:59'. BETWEEN is inclusive on both sides, which is what makes this pair cover every full day from June 13 to December 19.
Step 2: Collapse sessions to users
user_sessions is one row per session and user_id is heavily skewed, so a busy account appears many times across the stretch. SELECT DISTINCT user_id turns the session log into the list of accounts the team actually reviews.
SELECT DISTINCT user_id
FROM user_sessions
WHERE session_start BETWEEN '2025-06-13' AND '2025-12-19 23:59:59'- Bare date upper bound
BETWEEN '...-06-13' AND '...-12-19'reads as inclusive but means up to 00:00:00 on the 19th. The final day is cut to a single second.- End-of-day upper bound
BETWEEN '...-06-13' AND '...-12-19 23:59:59'covers the full 19th, matching what the team asked for.
Common follow-up questions
- How would you write this with a half-open range instead, and why do many teams prefer it? (Tests whether the candidate knows `>= start AND < next_day` avoids end-of-day precision issues with fractional seconds.)
- The team now wants each user's session count in that stretch, busiest first. What changes? (Moves from set membership to a per-user count with an ordering.)
- What if `session_start` were stored in UTC but the security team means local time? (Tests awareness that day boundaries depend on the time zone the business means.)