User Sessions on Specific Days
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
- Meta
Problem
Pull every recorded field from user_sessions for the sessions that started on a Saturday or a Monday, the two days the growth team is setting side by side in its weekly engagement review.
One user. Specific days. What happened?
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
| session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed |
|---|---|---|---|---|---|
| 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 |
| 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 |
| 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 |
| 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 |
| 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 |
Worked solution and explanation
Two days that never sit together
This is a calendar-recurrence filter in an engagement-review costume. Saturday and Monday come back every week of a multi-year table and never touch each other, so no date range or BETWEEN on session_start can describe them. You have to read the weekday off every row and match two separate codes. Everyone writes the extraction. What separates candidates is the numbering: STRFTIME('%w', ...) counts 0 for Sunday through 6 for Saturday, and the ISO habit of Monday=1 through Sunday=7 quietly hands you the wrong day. The query still runs and still returns rows, and every wrong row looks plausible.
Break down the requirements
Step 1: Read the weekday off each start time
session_start is a full timestamp, and the weekday is not stored anywhere. STRFTIME('%w', session_start) turns each one into a single-character code, '0' for Sunday up to '6' for Saturday. It returns text, so compare it to text.
Step 2: Match two codes, not a range
Monday is '1' and Saturday is '6'. They are not adjacent, so a BETWEEN '1' AND '6' would sweep in the whole working week. List the two codes with IN ('1', '6') and keep every column with SELECT *, since the review wants the full session record.
The solution
SELECT *
FROM user_sessions
WHERE STRFTIME('%w', session_start) IN ('1', '6')Common follow-up questions
- How would you return weekend sessions only, Saturday and Sunday? (Swap the list to `IN ('0', '6')`: the codes sit at opposite ends of the numbering.)
- What if `session_start` stored only a time of day, with no date? (A weekday needs a date; you would have to bring one in from another column.)
- How would you count sessions per day of the week? (Group on `STRFTIME('%w', session_start)` and count rows per code.)