# User Sessions on Specific Days

> One user. Specific days. What happened?

Canonical URL: <https://datadriven.io/problems/user_sessions_on_specific_days>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Pull all available fields for sessions belonging to user 197, but only sessions that started on a Saturday or Monday.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests date function usage combined with multi-condition filtering. Interviewers check whether you can extract the day of week from a timestamp and filter on specific day names.

---

### Break down the requirements

#### Step 1: Filter to user 197

`WHERE user_id = 197` restricts to sessions for the specific user.

#### Step 2: Filter to Saturday or Monday

Use `STRFTIME('%w', session_start)` to extract the day of week (0=Sunday, 1=Monday, 6=Saturday), then filter with `IN ('1', '6')`.

---

### The solution

**Day-of-week filter for specific user**

```sql
SELECT *
FROM user_sessions
WHERE user_id = 197
  AND STRFTIME('%w', session_start) IN ('1', '6')
```

> **Cost Analysis**
>
> An index on `user_id` narrows 40M rows to ~13 rows (for a single user). The STRFTIME computation on ~13 rows is negligible. This is very fast with proper indexing.

> **Interviewers Watch For**
>
> Knowing the correct day-of-week encoding. In SQLite, `%w` returns 0 for Sunday through 6 for Saturday. Getting Monday (1) and Saturday (6) correct is the key.

> **Common Pitfall**
>
> Using the wrong day numbers. Different SQL engines use different encodings: PostgreSQL's `EXTRACT(DOW ...)` uses 0=Sunday, while `EXTRACT(ISODOW ...)` uses 1=Monday through 7=Sunday.

---

## Common follow-up questions

- How would you filter for weekends only (Saturday and Sunday)? _(Change IN to ('0', '6') for Sunday and Saturday.)_
- What if session_start stored timestamps without dates? _(You would need a separate date column or combine with a date to extract the day of week.)_
- How would you count sessions per day of week for this user? _(GROUP BY STRFTIME('%w', session_start) with COUNT(*).)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/user_sessions_on_specific_days)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.