Katkat
Back

Identify Session with Maximum Overlapping Sessions Count

PayPalData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question evaluates temporal interval reasoning, set-based querying and aggregation skills, focusing on identifying and counting overlapping time intervals using SQL or equivalent Python data-manipulation tools.

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.