logo

Back to questions

User Concurrent Sessions

Hard

This is the same question as problem #26 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table containing information on user sessions, including their start and end times. Write a query to obtain the user session that is concurrent with the other user sessions.

Output the session id and number of concurrent user sessions in descending order.

Assumptions:

  • Sessions with the same start and end time are not considered concurrent sessions.
  • Concurrent sessions are defined as overlapping sessions:
  1. If session 1 starts first, then session 2's start time is greater than or lesser than session 1’s end time.
  2. If session 2 starts first, then session 1’s end time is greater than or lesser than session 2’s start time.

Table:

Column NameType
session_idinteger
start_timedatetime
end_timedatetime

Example Input:

session_idstart_timeend_time
74638201/02/2022 12:00:0002/01/2022 16:48:00
14314501/02/2022 14:25:0002/01/2022 15:05:00
13451401/02/2022 15:23:0002/01/2022 18:15:00
24235401/02/2022 21:34:0003/01/2022 00:11:00
14325601/06/2022 06:55:0001/06/2022 09:05:00

Example Output:

session_idconcurrent_sessions
7463822
1432561

Explanation

Session 746382 has 2 concurrent sessions which are session_id 134514 and 242354.

  • Session 746382's end time, 02/01/2022 16:48:00, is before session 134514's end time of 02/01/2022 18:15:00.
  • Session 746382's end time, 02/01/2022 16:48:00, is before session 242354's end time of 03/01/2022 00:11:00.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.