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 mytable (user_id int, datetime timestamp, direction varchar(3));
CREATE TABLE
insert into mytable (user_id, datetime, direction) values
(1, timestamp '2025-02-17 18:25:02', 'in'),
(1, timestamp '2025-02-17 20:09:10', 'out'),
(2, timestamp '2025-02-17 09:55:57', 'in'),
(2, timestamp '2025-02-17 20:48:37', 'out');
INSERT 0 4
WITH input AS (
SELECT *
, tsrange(lag(datetime) OVER inout, datetime, '[)') AS timeframe
FROM mytable
WINDOW inout as (PARTITION BY user_id ORDER BY datetime)
)
SELECT ts,
count(user_id)
FROM generate_series('2025-02-17 08:00'::timestamp, '2025-02-18 14:00'::timestamp, interval '1 hour') g(ts)
LEFT JOIN input ON timeframe && tsrange(ts, ts + interval '1 hour')
AND direction = 'out'
GROUP BY 1
ORDER BY 1;
| ts | count |
|---|---|
| 2025-02-17 08:00:00 | 0 |
| 2025-02-17 09:00:00 | 1 |
| 2025-02-17 10:00:00 | 1 |
| 2025-02-17 11:00:00 | 1 |
| 2025-02-17 12:00:00 | 1 |
| 2025-02-17 13:00:00 | 1 |
| 2025-02-17 14:00:00 | 1 |
| 2025-02-17 15:00:00 | 1 |
| 2025-02-17 16:00:00 | 1 |
| 2025-02-17 17:00:00 | 1 |
| 2025-02-17 18:00:00 | 2 |
| 2025-02-17 19:00:00 | 2 |
| 2025-02-17 20:00:00 | 2 |
| 2025-02-17 21:00:00 | 0 |
| 2025-02-17 22:00:00 | 0 |
| 2025-02-17 23:00:00 | 0 |
| 2025-02-18 00:00:00 | 0 |
| 2025-02-18 01:00:00 | 0 |
| 2025-02-18 02:00:00 | 0 |
| 2025-02-18 03:00:00 | 0 |
| 2025-02-18 04:00:00 | 0 |
| 2025-02-18 05:00:00 | 0 |
| 2025-02-18 06:00:00 | 0 |
| 2025-02-18 07:00:00 | 0 |
| 2025-02-18 08:00:00 | 0 |
| 2025-02-18 09:00:00 | 0 |
| 2025-02-18 10:00:00 | 0 |
| 2025-02-18 11:00:00 | 0 |
| 2025-02-18 12:00:00 | 0 |
| 2025-02-18 13:00:00 | 0 |
| 2025-02-18 14:00:00 | 0 |
SELECT 31