Given User log

userID logintime           logouttime
123    2020/09/03  10:20   2020/09/03  12:30
124    2020/09/03  1:20    2020/09/03  2:30
125    2020/09/03  2:20    2020/09/03  3:30
126    2020/09/03  3:00    2020/09/03  4:30
127    2020/09/03  2:00    2020/09/03  4:30
128    2020/09/03  1:00    2020/09/03  4:30

The goal: Find the peak Logged-in(Active) users in any time? This means that I need to return the timestamp which had the most active session and the number of active sessions in that time stamp. For the above table the result should be:

    timestamp        max
    2020/09/03  2:20  4

as in this timestamp there are 4 active sesion and that is the highest peak.

There is a similar question with Sql Server sql server table peak time but I'm looking to solve this in PostgresSQL

This is what I tried:

Select logintime as time, "login"
from log l1
unoin
Select logouttime as time, "logout"
join log l2
order by time

This should give me 1 ordered time column and now it should be possible to do window count (login 1 and logout -1) then the max number in count column is the most active sessions.

I'm having trouble in implementing this logic in SQL statment

CodePudding user response：

Here it is. The timedata CTE is a mimic of the real user log table.

with timedata (userid, logintime, logouttime) as 
(
 values
 (123, '2020-09-03 10:20'::timestamp, '2020-09-03 12:30'::timestamp),
 (124, '2020-09-03 1:20' , '2020-09-03 2:30' ),
 (125, '2020-09-03 2:20' , '2020-09-03 3:30' ),
 (126, '2020-09-03 3:00' , '2020-09-03 4:30' ),
 (127, '2020-09-03 2:00' , '2020-09-03 4:30' ),
 (128, '2020-09-03 1:00' , '2020-09-03 4:30' )
),
logged_in_count as 
(
 select
   t as point_in_time, 
   (select count(*) from timedata where t between logintime and logouttime) as cnt
 from (select distinct logintime t from timedata) as points_in_time
)
select * from logged_in_count where cnt = (select max(cnt) from logged_in_count);

In short, make a list of all distinct login times and count how many users are logged for each of them (login_count CTE) and then select the ones with the highest count. No LEAD or LAG are used or re-implemented. You may however use another list of points in time, i.e. generate_series.