The Crowded Hour

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

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.

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

Tables

user_sessions

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

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

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

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.

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