concurrent users sql

Question

I am trying to calculate number of concurrent users in a time range. The input looks something like the below

loginid login_time  logout_time
1   2016-06-08 09:10:00 2016-06-08 10:10:00
2   2016-06-08 08:55:00 2016-06-08 10:55:00
3   2016-06-08 09:29:00 2016-06-08 10:29:00
4   2016-06-08 09:40:00 2016-06-08 10:40:00
5   2016-06-08 09:08:00 2016-06-08 10:08:00
6   2016-06-08 09:04:00 2016-06-08 10:04:00
7   2016-06-08 09:12:00 2016-06-08 10:12:00
8   2016-06-08 09:40:00 2016-06-08 10:40:00
9   2016-06-08 09:21:00 2016-06-08 10:21:00

How to get the concurrent users in time range ?

something like the below

Date              No of Users
2016-06-08 09:00:00 1
2016-06-08 09:30:00 7
2016-06-08 09:45:00 9
2016-06-08 10:00:00 9
2016-06-08 10:15:00 5
2016-06-08 10:30:00 3

Will we need a reference table or can we generate the range from the login table ?

Answer 1

You can generate the times you want using a recursive CTE and then use a correlated subquery:

with recursive times as (
      select timestamp '2016-06-08 09:00:00' as tme
      union all
      select tme + interval 30 minute
      from times
      where tme < '2016-06-08 12:00:00'
     )
select tme,
       (select count(*)
        from t
        where t.login_time <= times.tme and
              t.logout_time > times.tme
       ) as cnt
from times;