SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table sessions (
user_id int,
timestamp int
);

insert into sessions values
(1000,	1661919816),
(1000,	1661919861),
(1001,	1661919816);
3 rows affected

SELECT 
    user_id,
    timestamp,
    SUM(flag) OVER (ORDER BY user_id, timestamp) session_id
FROM (    
SELECT
    *,
    CASE WHEN user_id = LAG(user_id) OVER (ORDER BY user_id, timestamp)
              AND timestamp - LAG(timestamp, 1, 0) OVER (ORDER BY user_id, timestamp) < 60
         THEN 0
         ELSE 1
    END flag
FROM sessions
) flags


user_id
timestamp
session_id


1000
1661919816
1

1000
1661919861
1

1001
1661919816
2


insert into sessions values

(1000,	1661919871),
(1000,	1661919931),

(1001,	1661919896),
(1001,	1661919906);
4 rows affected

SELECT 
    user_id,
    timestamp,
    SUM(flag) OVER (ORDER BY user_id, timestamp) session_id
FROM (    
SELECT
    *,
    CASE WHEN user_id = LAG(user_id) OVER (ORDER BY user_id, timestamp)
              AND timestamp - LAG(timestamp, 1, 0) OVER (ORDER BY user_id, timestamp) < 60
         THEN 0
         ELSE 1
    END flag
FROM sessions
) flags


user_id
timestamp
session_id


1000
1661919816
1

1000
1661919861
1

1000
1661919871
1

1000
1661919931
2

1001
1661919816
3

1001
1661919896
4

1001
1661919906
4

user_id	timestamp	session_id
1000	1661919816	1
1000	1661919861	1
1000	1661919871	1
1000	1661919931	2
1001	1661919816	3
1001	1661919896	4
1001	1661919906	4