By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH
-- your input ...
indata(id,ts) AS (
SELECT 1,{ts '2022-09-23 15:01:00'}
UNION ALL SELECT 2,{ts '2022-09-23 15:02:00'}
UNION ALL SELECT 3,{ts '2022-10-03 14:52:00'}
UNION ALL SELECT 4,{ts '2022-10-03 14:53:00'}
UNION ALL SELECT 5,{ts '2022-10-03 14:54:00'}
UNION ALL SELECT 6,{ts '2022-10-03 14:56:00'}
UNION ALL SELECT 7,{ts '2022-10-03 14:57:00'}
UNION ALL SELECT 8,{ts '2022-10-03 14:58:00'}
UNION ALL SELECT 9,{ts '2022-10-03 14:59:00'}
)
-- real query starts here - replace following comma with "WITH"
,
-- sessionization part 1: counter at 1 if gap > 1 min
sess1 AS (
SELECT
*
,CASE
WHEN DATEDIFF(minute,LAG(ts) OVER(ORDER BY ts), ts) = 1 THEN 0
ELSE 1
END AS counter
FROM indata
)
,
-- get the running sum of the obtained counter above to get a session id
sess2 AS (
SELECT
id
, ts
, SUM(counter) OVER(ORDER BY ts) AS session_id
FROM sess1
)
SELECT
session_id
| session_id | from_ts | to_ts |
|---|---|---|
| 1 | 2022-09-23 15:01:00.000 | 2022-09-23 15:02:00.000 |
| 2 | 2022-10-03 14:52:00.000 | 2022-10-03 14:54:00.000 |
| 3 | 2022-10-03 14:56:00.000 | 2022-10-03 14:59:00.000 |