I have a table named events
like this:
x
id: int
source_id: int
start_datetime: timestamp
end_datetime: timestamp
These events could have overlaps, and I want to know maximum number of overlapping events that have occurred over a time span. For example, in a situation like this:
id | source_id | start_datetime | end_datetime
----------------------------------------------------------
1 | 23 | 2017-1-1T10:20:00 | 2017-1-1T10:40:00
1 | 42 | 2017-1-1T10:30:00 | 2017-1-1T10:35:00
1 | 11 | 2017-1-1T10:37:00 | 2017-1-1T10:50:00
The answer is 2, because at most 2 events overlap at 10:30 until 10:35.
I’m using Postgres 9.6
Advertisement
Answer
Here is the idea: count the number of starts and subtract the number of stops. That gives the net amount at each time. The rest is just aggregation:
with e as (
select start_datetime as dte, 1 as inc
from events
union all
select end_datetime as dte, -1 as inc
from events
)
select max(concurrent)
from (select dte, sum(sum(inc)) over (order by dte) as concurrent
from e
group by dte
) e;
The subquery shows the number of overlapping events at each time.
You can get the time frame as:
select dte, next_dte, concurrent
from (select dte, sum(sum(inc)) over (order by dte) as concurrent,
lead(dte) over (partition by dte) as next_dte
from e
group by dte
) e
order by concurrent desc
fetch first 1 row only;