By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE myTable (
rowid int,
userid text,
webid text,
ts timestamp
);
INSERT INTO myTable
(`rowid`, `userid`, `webid`, `ts`)
VALUES ('1', 'A', '34', '2023-01-31 16:34:49.000'),
('2', 'A', '73', '2023-01-31 16:34:50.000'),
('3', 'A', '97', '2023-01-31 16:34:58.000'),
('4', 'A', '17', '2023-01-31 17:35:02.000'),
('5', 'A', '17', '2023-01-31 17:35:07.000'),
('6', 'A', '17', '2023-01-31 17:35:18.000'),
('7', 'A', '17', '2023-01-31 17:35:30.000'),
('8', 'A', '1', '2023-01-31 17:35:37.000'),
('9', 'A', '1', '2023-01-31 17:35:38.000'),
('10', 'A', '77', '2023-01-31 17:35:41.000'),
('11', 'A', '77', '2023-01-31 17:35:42.000'),
('12', 'A', '15', '2023-01-31 17:35:42.000'),
('13', 'A', '44', '2023-01-31 17:35:42.000'),
('14', 'A', '1', '2023-01-31 17:35:42.000'),
('15', 'A', '77', '2023-01-31 17:35:45.000'),
('16', 'A', '44', '2023-01-31 17:35:45.000'),
('17', 'A', '1', '2023-01-31 17:37:10.000'),
('18', 'A', '1', '2023-01-31 17:37:12.000'),
('19', 'A', '77', '2023-01-31 17:37:14.000'),
('20', 'A', '77', '2023-01-31 17:52:14.000'),
('21', 'A', '77', '2023-01-31 18:12:14.000'),
('22', 'A', '77', '2023-01-31 18:45:14.000'),
('23', 'A', '77', '2023-01-31 18:55:15.000'),
('1', 'B', '33', '2023-01-31 06:37:15.000'),
('2', 'B', '56', '2023-01-31 06:40:15.000')
;
Records: 25 Duplicates: 0 Warnings: 0
SELECT rowid, userid, webid, session_id, ts,
MIN(ts) OVER (PARTITION BY userid, session_id ORDER BY ts, rowid) first_ts
FROM (
SELECT *,
1 + COUNT(CASE WHEN webid <> prev_webid OR ts > prev_ts + INTERVAL '30' MINUTE THEN 1 END)
OVER (PARTITION BY userid ORDER BY ts, rowid) session_id
FROM (
SELECT *,
LAG(webid) OVER (PARTITION BY userid ORDER BY ts, rowid) prev_webid,
LAG(ts) OVER (PARTITION BY userid ORDER BY ts, rowid) prev_ts
FROM myTable
) t
) t
| rowid | userid | webid | session_id | ts | first_ts |
|---|---|---|---|---|---|
| 1 | A | 34 | 1 | 2023-01-31 16:34:49 | 2023-01-31 16:34:49 |
| 2 | A | 73 | 2 | 2023-01-31 16:34:50 | 2023-01-31 16:34:50 |
| 3 | A | 97 | 3 | 2023-01-31 16:34:58 | 2023-01-31 16:34:58 |
| 4 | A | 17 | 4 | 2023-01-31 17:35:02 | 2023-01-31 17:35:02 |
| 5 | A | 17 | 4 | 2023-01-31 17:35:07 | 2023-01-31 17:35:02 |
| 6 | A | 17 | 4 | 2023-01-31 17:35:18 | 2023-01-31 17:35:02 |
| 7 | A | 17 | 4 | 2023-01-31 17:35:30 | 2023-01-31 17:35:02 |
| 8 | A | 1 | 5 | 2023-01-31 17:35:37 | 2023-01-31 17:35:37 |
| 9 | A | 1 | 5 | 2023-01-31 17:35:38 | 2023-01-31 17:35:37 |
| 10 | A | 77 | 6 | 2023-01-31 17:35:41 | 2023-01-31 17:35:41 |
| 11 | A | 77 | 6 | 2023-01-31 17:35:42 | 2023-01-31 17:35:41 |
| 12 | A | 15 | 7 | 2023-01-31 17:35:42 | 2023-01-31 17:35:42 |
| 13 | A | 44 | 8 | 2023-01-31 17:35:42 | 2023-01-31 17:35:42 |
| 14 | A | 1 | 9 | 2023-01-31 17:35:42 | 2023-01-31 17:35:42 |
| 15 | A | 77 | 10 | 2023-01-31 17:35:45 | 2023-01-31 17:35:45 |
| 16 | A | 44 | 11 | 2023-01-31 17:35:45 | 2023-01-31 17:35:45 |
| 17 | A | 1 | 12 | 2023-01-31 17:37:10 | 2023-01-31 17:37:10 |
| 18 | A | 1 | 12 | 2023-01-31 17:37:12 | 2023-01-31 17:37:10 |
| 19 | A | 77 | 13 | 2023-01-31 17:37:14 | 2023-01-31 17:37:14 |
| 20 | A | 77 | 13 | 2023-01-31 17:52:14 | 2023-01-31 17:37:14 |
| 21 | A | 77 | 13 | 2023-01-31 18:12:14 | 2023-01-31 17:37:14 |
| 22 | A | 77 | 14 | 2023-01-31 18:45:14 | 2023-01-31 18:45:14 |
| 23 | A | 77 | 14 | 2023-01-31 18:55:15 | 2023-01-31 18:45:14 |
| 1 | B | 33 | 1 | 2023-01-31 06:37:15 | 2023-01-31 06:37:15 |
| 2 | B | 56 | 2 | 2023-01-31 06:40:15 | 2023-01-31 06:40:15 |