SQL – Get the MAX COUNT access peak having only entrance and exit date

I have a table that keeps the access to our system, and it have two columns called entranceTime and exitTime

Eg:

entranceTime            exitTime
2021-09-09 20:03:31+00  2021-09-09 20:05:39+00
2021-09-09 20:03:58+00  2021-09-09 20:05:11+00
2021-09-09 20:04:29+00  2021-09-09 20:05:36+00
2021-09-09 20:04:09+00  2021-09-09 20:04:28+00
2021-09-09 20:05:15+00  2021-09-09 20:05:30+00

What im trying to get is the “peak” of simultaneous connections, i tried using a subquery with MAX and COUNT like this:

SELECT Max((SELECT Count("a"."id")
            FROM   "public"."access" "a"
            WHERE  "a"."entrancetime" >= "a2"."entrancetime"
                   AND a."entrancetime" <= "a2"."exittime"))
FROM   "public"."access" a2

But with this query im getting the count of 5 instad of 3, is it possible to achieve this with only this two columns and using only SQL? Thank you for your time.

Answer

The “peak” is going to occur at one of the entrance times. You can use a correlated subquery to count the number of concurrent rows at each one. And then get the peak:

select max(num_concurrent)
from (select a.*,
             (select count(*)
              from "public"."access" a2
              where a2.exittime >= a.entrancetime and
                    a2.entrancetime <= a.entrancetime
             ) as num_concurrent
      from "public"."access" a
     ) a;

The above may not scale well, so there is actually a more efficient way. You can unpivot the times and use cumulative sums:

select v.t, sum(v.inc) as net_inc,
       sum(sum(v.inc)) over (order by v.t) as num_concurrent
from "public"."access" a cross join lateral
     (values (a.entrancetime, 1), (a.exittime, -1)
     ) v(t, inc)
group by v.t
order by num_concurrent desc
limit 1;

Here is a db<>fiddle.