# The Crowded Hour

> Every device type has a moment when the room is fullest. Find it.

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

Domain: SQL · Difficulty: easy · Seniority: L5 · Asked in: Amazon

## Problem

We size capacity per device type to its worst-case crowding, not its average, and this quarter there is only budget to reinforce the three most strained types. For each type, find the busiest rolling 45-day window, the stretch that saw the most different people active on it, and report those three types with that peak headcount and the window it covers, most crowded first.

## Tables

### user_sessions

- session_id INTEGER
- user_id INTEGER
- device_id INTEGER
- session_start TEXT
- session_duration_sec INTEGER
- pages_viewed INTEGER

Sample rows (10 of 200):

| 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 |

### devices

- device_id INTEGER
- device_type TEXT
- os_name TEXT
- os_version TEXT
- browser TEXT

Sample rows (10 of 200):

| device_id | device_type | os_name | os_version | browser |
| --- | --- | --- | --- | --- |
| 201 | desktop | Android | 15.1 | Chrome |
| 230 | tablet | Windows | 16.2 | Firefox |
| 259 | smart_tv | macOS | 17.0 | Edge |
| 288 | wearable | Linux | 11 | Opera |
| 317 | mobile | tvOS | 12 | Samsung Internet |
| 346 | desktop | watchOS | 13 | NULL |
| 375 | tablet | iOS | 10.0.19041 | Safari |
| 404 | smart_tv | Android | 14.1 | Chrome |
| 433 | wearable | Windows | NULL | Firefox |
| 462 | mobile | macOS | 14.0 | Edge |

## Expected output

First 3 of 3 rows:

| device_type | window_span | peak_active_users |
| --- | --- | --- |
| smart_tv | 2026-02-27 to 2026-04-13 | 7 |
| tablet | 2026-05-04 to 2026-06-18 | 7 |
| desktop | 2026-02-11 to 2026-03-28 | 6 |

## Worked solution and explanation

### What this really is

Strip off the capacity-planning costume and this is an interval-overlap self-join. The question is not how many people used each device type in total; it is how crowded the busiest rolling stretch ever got, then which types get crowded hardest. A plain `GROUP BY session_start` cannot see that: two sessions starting days apart still share a rolling window, and grouping by a timestamp counts each row alone. The skill is comparing every session against every other of the SAME device type, counting the DISTINCT users caught in each window, keeping the single fullest window per type, then surfacing only the three most strained. Miss the `DISTINCT` and one regular's repeat visits inflate the crowd; bucket by calendar month and you report the wrong peak; keep the orphan sessions and a `NULL` device type sneaks into the ranking.

---

### Break down the requirements

#### Step 1: Attach the device type, dropping the orphans

Inner-join `user_sessions` to `devices` on `device_id` to attach `device_type`. The inner join is deliberate: a session with a null `device_id`, or one absent from `devices`, has no type, so it drops instead of polluting a bucket. Park the result in a `device_sessions` CTE for the self-join.

#### Step 2: Count the distinct crowd inside each rolling window

Self-join `device_sessions` on the SAME `device_type`, keeping the other session only when its `session_start` falls in the anchor's forward window (`b.session_start >= a.session_start AND b.session_start < datetime(a.session_start, '+45 days')`), and take `COUNT(DISTINCT b.user_id)` as the crowd. The distinct is load-bearing: a person with several sessions is one body, not several; dropping it pushes `smart_tv` from 7 to 8 and reorders the top types. This per-anchor rolling window is exactly what a `GROUP BY` on `session_start` cannot express.

#### Step 3: Keep the single fullest window per device type

`ROW_NUMBER() OVER (PARTITION BY device_type ORDER BY active_users DESC, window_start ASC)` numbers each type's windows fullest-first, breaking a reach tie by the earliest window. Keep `window_rank = 1` and you have the single peak window per device type.

#### Step 4: Order by the peak, take the top three

`ORDER BY peak_active_users DESC, device_type ASC` then `LIMIT 3`, since only three types get reinforced this quarter. Build the label with `substr(window_start, 1, 10) || ' to ' || date(window_start, '+45 days')`. The second sort key makes the order total, so the two types tied at 7 (`smart_tv`, `tablet`) land predictably, and the `LIMIT` deliberately cuts the two calmest (`mobile`, `wearable`); drop it and you report all five.

---

### The solution

**Three most crowded device types by peak rolling 45-day crowd**

```sql
WITH device_sessions AS (
    SELECT us.session_id, us.user_id, us.session_start, d.device_type
    FROM user_sessions us
    INNER JOIN devices d ON us.device_id = d.device_id
),
window_reach AS (
    SELECT a.device_type,
           a.session_start AS window_start,
           COUNT(DISTINCT b.user_id) AS active_users
    FROM device_sessions a
    INNER JOIN device_sessions b
      ON b.device_type = a.device_type
     AND b.session_start >= a.session_start
     AND b.session_start < datetime(a.session_start, '+45 days')
    GROUP BY a.device_type, a.session_id, a.session_start
),
ranked AS (
    SELECT device_type,
           substr(window_start, 1, 10) || ' to ' || date(window_start, '+45 days') AS window_span,
           active_users,
           ROW_NUMBER() OVER (
               PARTITION BY device_type
               ORDER BY active_users DESC, window_start ASC
           ) AS window_rank
    FROM window_reach
)
SELECT device_type, window_span, active_users AS peak_active_users
FROM ranked
WHERE window_rank = 1
ORDER BY peak_active_users DESC, device_type ASC
LIMIT 3
```

**Calendar-month GROUP BY**

Grouping by `strftime('%Y-%m', session_start)` snaps every crowd to a month boundary. A crowd peaking across the last week of one month and the first three of the next is split in half and never seen. The reported peak is an artifact of where the calendar happens to cut.

**Rolling-window self-join**

Anchoring a 45-day window at every session lets it land wherever the crowd actually is. The self-join gives each anchor its own window, which no `GROUP BY` on a bare timestamp can reproduce.

> **Sessions are not people**
>
> Using `COUNT(*)` instead of `COUNT(DISTINCT user_id)` is the sessions-are-not-people mistake: every repeat visit counts as another body, inflating each peak and lifting `smart_tv` to 8 here. The other common miss is a `LEFT JOIN` to `devices`, which keeps device-less sessions and quietly files them under a `NULL` device type.

> **A rolling window is not a GROUP BY**
>
> The tell is whether they reach for a self-join once they see a rolling window is not a `GROUP BY`, whether `DISTINCT` shows up so a regular is not counted many times, and whether the final `ORDER BY` is total before the `LIMIT` cuts to three. Saying 'a month bucket would miss a crowd straddling the boundary' shows the seniority the question probes for.

> **The quadratic scan gives way to a sweep-line**
>
> The self-join is O(n^2) per device type and `COUNT(DISTINCT ...)` forces per-window de-duplication of `user_id`. At 40M sessions this brute force will not fly. In production you sort per type by start and run a sweep-line: a moving 45-day frontier with a rolling distinct-user structure turns the quadratic scan into one ordered pass. An HLL estimate per window keeps memory flat when exactness is optional.

---

## Common follow-up questions

- What changes if capacity is sized on a rolling 7-day window instead of 45 days? _(Tests whether they can shrink the window without changing the query shape, and see that a tighter window generally lowers every peak.)_
- How would you break the peak crowd down by operating system within each device type? _(Tests adding a second partition dimension (`device_type` and `os_name`) while keeping the distinct-crowd and single-peak semantics intact.)_
- If this had to run over billions of sessions, how would you avoid the self-join entirely? _(Tests understanding that the quadratic self-join does not scale and that a sweep-line or sessionized rolling count is the production shape.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_crowded_hour)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.