In SQL, how can you calculate maximum concurrent number of users

Question

I have a table.

this table has usage period of user.

example is follow

|user_id|start_at  |end_at    |
|1      |2014/11/02|2014/11/05|
|2      |2014/11/03|2014/11/07|
|3      |2014/11/08|2014/11/09|

I would like to get maximum concurrent number of users

above case,

Between 2014/11/03 ~ 2014/05, 2 user used.

I would like to get this "2".

Is there an easy way to set this up? What do you recommend?

possible duplicate of [SQL COUNT between dates in two different column](http://stackoverflow.com/questions/16384273/sql-count-between-dates-in-two-different-column) — Tab Alleman, Nov 14 '14 at 13:48

score 3 · Accepted Answer · answered Nov 14 '14 at 12:59

3

The maximum is going to occur at one of the start_at times. So, you can do this with a self-join:

select u.start_at, count(*) as numconcurrent
from usage u join
     usage u2
     on u.start_at between u2.start_at and u2.end_at
group by u.start_at

If you want the maximum number, then add:

order by count(*) desc
limit 1;

answered Nov 14 '14 at 12:59

Gordon Linoff

1

Thank you! I could get max count. – tamagohan2 Nov 14 '14 at 13:42
Thank you for rescuing me from my brain. I was convinced I'd need a recursive CTE or something coupled with weird windowing functions. – Spivonious Mar 07 '22 at 22:09

score 1 · Answer 2 · answered Nov 14 '14 at 13:01

1

sounds like you may have mis-stated the question. Do you really mean "max" ? or just the number of users between two dates ? If that is so, then

Select count(*) from table 
where start_at < @end
   and end_at > @start

answered Nov 14 '14 at 13:01

Charles Bretana

then you need to rephrase your question, because it is not clear what you mean by "max" – Charles Bretana Nov 14 '14 at 13:46
Sorry and hank you for pointing this out. I rephrase my question. – tamagohan2 Nov 15 '14 at 01:07

score 0 · Answer 3 · edited Nov 14 '14 at 13:05

0

sum(case when start_at >= @startdate and end_at <= @enddate 
          then 1 
          else 0 
    end) as total

this would follow a qualifying set declare statement or embedded in a stored procedure. Your choice.

This will increment by 1 every time it sees a row that meets those credentials and sum the total.

edited Nov 14 '14 at 13:05

Saravana Kumar

answered Nov 14 '14 at 12:59

SFrejofsky

3 Answers3