Identify Session with Maximum Overlapping Sessions Count

sessions

| session_id | start_time           | end_time             |
| 1          | 2023-01-01 09:00:00 | 2023-01-01 10:00:00 |
| 2          | 2023-01-01 09:30:00 | 2023-01-01 11:00:00 |
| 3          | 2023-01-01 10:30:00 | 2023-01-01 12:00:00 |
| 4          | 2023-01-01 13:00:00 | 2023-01-01 14:00:00 |

Scenario

SQL screen – session overlap analysis

Question

Given the sessions table, write SQL to return the session_id that overlaps with the greatest number of other sessions and the overlap count.

Hints

Self-join on intervals where start_a < end_b AND end_a > start_b; aggregate and order by count desc.

Session With Most Overlapping Sessions

Given the sessions table, write SQL to return the session_id that overlaps with the greatest number of other sessions and the overlap count. If multiple sessions tie for the maximum overlap count, return the one with the smallest session_id.

Table sessions

column	type	constraints
session_id	INTEGER	PRIMARY KEY, NOT NULL
start_time	TIMESTAMP	NOT NULL
end_time	TIMESTAMP	NOT NULL

Sample data:

end_time	session_id	start_time
2023-01-01 10:00:00	1	2023-01-01 09:00:00
2023-01-01 11:00:00	2	2023-01-01 09:30:00
2023-01-01 12:00:00	3	2023-01-01 10:30:00
2023-01-01 14:00:00	4	2023-01-01 13:00:00

Expected output

Return a single row: the session_id that overlaps with the most other sessions and the corresponding overlap_count. If multiple sessions tie, return the one with the smallest session_id.

overlap_count	session_id
2	2

Hints

1. Self-join the table on interval overlap: a.start_time < b.end_time AND a.end_time > b.start_time.
2. Aggregate by a.session_id to count overlaps; include zero-overlap sessions, then order by overlap count DESC and session_id ASC for tie-breaking, and limit to 1.