By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE usersessions (userid int, starttime timestamp, endtime timestamp);
INSERT INTO usersessions (userid, starttime, endtime)
VALUES
(1, '01-Oct-19 6:00:00 AM', '01-Oct-19 11:10:00 AM'),
(2, '01-Oct-19 11:00:00 AM', '01-Oct-19 4:00:00 PM'),
(3, '01-Oct-19 10:30:00 AM', '01-Oct-19 4:00:00 PM'),
(4, '2019-10-02 06:00:00', '2019-10-02 11:30:00'), -- does not overlap
(5, '2019-10-02 06:00:00', '2019-10-02 11:30:01'), -- overlaps
(6, '2019-10-02 11:00:00', '2019-10-02 16:00:00'),
(7, '2019-10-02 10:30:00', '2019-10-02 16:00:00')
;
7 rows affected
-- calculating for all of Oct 2019
WITH range AS (SELECT timestamp '2019-10-01' AS start_ts -- incl. lower bound
, timestamp '2019-11-01' AS end_ts) -- excl. upper bound
, cte AS (
SELECT userid, starttime
-- default to current timestamp if NULL
, COALESCE(endtime, localtimestamp) - interval '30 min' AS endtime
FROM usersessions, range r
WHERE starttime < r.end_ts -- count overlaps *starting* in outer time range
AND (endtime >= r.start_ts + interval '30 min' OR endtime IS NULL)
)
, ct AS (
SELECT ts, sum(ct) OVER (ORDER BY ts, ct) AS session_ct
FROM (
SELECT endtime AS ts, -1 AS ct FROM cte
UNION ALL
SELECT starttime , +1 FROM cte
) sub
)
SELECT ts::date, max(session_ct) AS max_concurrent_sessions
FROM ct, range r
WHERE ts >= r.start_ts
AND ts < r.end_ts -- crop outer time range
GROUP BY ts::date
ORDER BY 1;
| ts | max_concurrent_sessions |
|---|---|
| 2019-10-01 | 2 |
| 2019-10-02 | 3 |