Overlapping User Sessions

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Vanguard

Problem

Each row of user_sessions is one session that starts at session_start and lasts session_duration_sec seconds; no end time is stored. Find every pair of two different sessions from the same user that overlap, meaning each starts before the other ends, and return the user_id with both session_ids and both start times.

Two sessions, one user, same clock. Something overlaps.

Tables

user_sessions

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

Expected output

Worked solution and explanation

This is an all-pairs interval overlap dressed up as session analytics. Nothing stores when a session ends, so the first move is building the end from session_start plus session_duration_sec seconds. The real test is that overlap relates every session to every other session of the same user, not just to its neighbour. On this data, user 100's 10:00 session runs until noon and overlaps both the 11:00 and the 11:30 session. A sort-and-compare-the-next-row approach finds two pairs and silently drops the third. The self-join gets all the pairs. The trap is the join key: write a.session_id <> b.session_id and every pair comes back twice, mirrored.

Walking to the answer

Step 1: Derive the end that is not stored

datetime(session_start, '+' || session_duration_sec || ' seconds') produces the end in the same 'YYYY-MM-DD HH:MM:SS' text as session_start, so the two compare correctly as strings. A row whose session_duration_sec is NULL gets a NULL end and fails every comparison. That is the right outcome: a session of unknown length cannot be shown to overlap anything.

Step 2: Pair each session with its same-user peers, once

Self-join user_sessions on user_id with a.session_id < b.session_id. That one predicate does two jobs: it removes the session-with-itself row, and it keeps only one of the two mirrored orderings. The preview confirms the convention: the smaller id always sits in session_id_1.

Step 3: Test overlap from both sides

Keep the pair when a.session_start is before b's end and b.session_start is before a's end. The strict < means two sessions that only touch (one ends exactly as the other starts) do not count. The test is symmetric, which matters because a smaller session_id does not promise an earlier start.

Step 4: Return the pair, not the intervals

Project user_id, both ids as session_id_1 and session_id_2, and both starts as start_1 and start_2. The derived ends were only needed for the test.

SELECT
    a.user_id,
    a.session_id AS session_id_1,
    b.session_id AS session_id_2,
    a.session_start AS start_1,
    b.session_start AS start_2
FROM user_sessions a
JOIN user_sessions b
  ON a.user_id = b.user_id
  AND a.session_id < b.session_id
WHERE a.session_start < datetime(b.session_start, '+' || b.session_duration_sec || ' seconds')
  AND b.session_start < datetime(a.session_start, '+' || a.session_duration_sec || ' seconds')

One-directional test

b.session_start BETWEEN a.session_start AND a_end only catches b starting inside a. When the lower-id session starts later, the overlap is missed. BETWEEN is also inclusive, so touching sessions are wrongly flagged. On this seed the lower id always starts first, so the bug hides until real data arrives.

Two-sided test

a.session_start < b_end AND b.session_start < a_end is the negation of 'one ends before the other starts'. It catches containment, partial overlap and equal starts in either order, and it rejects touching sessions.

Common follow-up questions

• Per user, what is the peak number of sessions running at the same moment? (Moves from pairs to a sweep: +1 at each start, -1 at each end, and a running sum over the ordered events.)
• What if a NULL `session_duration_sec` means the session is still open? (Tests replacing a missing end with the current time instead of letting the row drop out.)
• How would you merge each user's overlapping sessions into continuous activity windows? (Gaps-and-islands: a new window starts when a session begins after the running maximum end.)
• One user has 50,000 sessions and the join stalls. What do you change? (Band joins, time bucketing, or splitting the hot key.)