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(
client_id VARCHAR(1) NOT NULL
,event VARCHAR(11) NOT NULL
,time time NOT NULL
);
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:00:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:05:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','purchase','14:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','12:04:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','12:07:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','13:20:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','12:00:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','12:07:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','add_to_cart','14:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','14:19:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','purchase','14:32:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','15:32:00');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select client_id, event, count(*) cnt, min(time) start_time, max(time) end_time
from (
select
t.*,
row_number() over(partition by client_id order by time) rn1,
row_number() over(partition by client_id, event order by time) rn2
from mytable t
) t
group by client_id, event, rn1 - rn2
order by client_id, min(time)
| client_id | event | cnt | start_time | end_time |
|---|---|---|---|---|
| A | view | 3 | 12:00:00 | 12:05:00 |
| A | purchase | 1 | 14:02:00 | 14:02:00 |
| B | view | 3 | 12:04:00 | 13:20:00 |
| C | view | 2 | 12:00:00 | 12:07:00 |
| C | add_to_cart | 1 | 14:02:00 | 14:02:00 |
| C | view | 1 | 14:19:00 | 14:19:00 |
| C | purchase | 1 | 14:32:00 | 14:32:00 |
| C | view | 1 | 15:32:00 | 15:32:00 |