SQL Coding Qs : Count of overlapping session.

OneLearn Course SQL
,
#1

Q: Assume you are given the below table on sessions from users, with a given start and end time. A session is concurrent with another session if they overlap in their start and end times. Write a query to output the session that is concurrent with the largest number of other sessions.
image

In above question their is a doubt regarding overlapping, we have write the query but it is right or wrong please tell us

Solution :

WITH session_start_end AS ( SELECT session_id, 
                                   start_time,
                                   DATE_ADD ( start_time, INTERVAL duration) AS end_time
                            FROM sessions)
SELECT session_id,
       COUNT(1) AS concurrent_session
FROM session_start_end  AS s1
SELF JOIN session_start_end  AS s2
ON s1.session_id != s2.session_id
AND s2.start_time BETWEEN (s1.start_time AND s1.end_time)
GROUP BY 1;
#2

First thing is for calculating the end time using DATE_ADD function (this function is not available in postgres it is from MYSQL)
you need to have syntax like this (assume duration is in seconds)

DATE_ADD ( start_time, INTERVAL duration SECOND) AS end_time

The next step is to determine the query logic for when the two sessions are concurrent.
Let’s say we have two sessions, session 1 and session 2. Note that there are two cases in which they overlap:

  1. If session 1 starts first, then the start time for session 2 is less than or equal to session 1’s end time.
  2. If session 2 starts first, then session 1’s end time for session 1 is greater than or equal to session 2’s start time.
    When we combine both cases, this simplifies to session 2’s start time falling between session 1’s start time and session 1’s end time.
WITH session_start_end AS ( SELECT session_id, 
                                   start_time,
                                   DATE_ADD ( start_time, INTERVAL duration SECOND) AS end_time
                            FROM sessions)
SELECT
  s1.session_id,
  s2.session_id
FROM session_start_end  AS s1
JOIN session_start_end  AS s2
  ON s1.session_id != s2.session_id
  AND s2.start_time BETWEEN (s1.start_time AND s1.end_time);

reasonings for having the two conditions in join:

  • First condition sessions_1.session_id != sessions_2.session_id: While we are looking for concurrent sessions, we also do not want sessions where session 1 starts the exact same time as sessions 2.
  • Second condition sessions_2.start_time BETWEEN sessions_1.start_time AND sessions_1.end_time: Interpreted as the combination of the two cases in the explanation above where session 2’s start time falls in between session 1’s start time and end time, or vice versa

In your code your join is correct but one thing is you do not use keyword SELF JOIN just use the word JOIN. (self-join is a regular join that joins a table to itself using the INNER JOIN or LEFT JOIN )

The above code gives you all the s1.session_id and its corresponding overlapping s2.session_id

But according to the second criteria you are only going to get those session from second table which has its start time between start and end time of session in 1st table i.e. you get all the s1.session_id and its corresponding overlapping s2.session_id.

This means that the session id coming from first table actually contains session id coming from second table.
So for finding how many session are concurrent (or contained inside another session id) you need to do group by s1.session_id and not just session_id.

WITH session_start_end AS ( SELECT session_id, 
                                   start_time,
                                   DATE_ADD ( start_time, INTERVAL duration SECOND) AS end_time
                            FROM sessions)
SELECT s1.session_id,
       COUNT(1) AS concurrent_session
FROM session_start_end  AS s1
JOIN session_start_end  AS s2
ON s1.session_id != s2.session_id
AND s2.start_time BETWEEN (s1.start_time AND s1.end_time)
GROUP BY 1;

Now one more things is left i.e. finding the session that is concurrent with the largest number of other sessions.
for that you can use the below code

WITH session_start_end AS ( SELECT session_id, 
                                   start_time,
                                   DATE_ADD ( start_time, INTERVAL duration SECOND) AS end_time
                            FROM sessions)
SELECT s1.session_id,
       COUNT(1) AS concurrent_session
FROM session_start_end  AS s1
JOIN session_start_end  AS s2
ON s1.session_id != s2.session_id
AND s2.start_time BETWEEN (s1.start_time AND s1.end_time)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1
#3

Ok thanks sir, i understand that.