SQL Server 2019 (Linux)

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

CREATE TABLE Subscriptions (
    SubscriptionID INT PRIMARY KEY,
    UserID INT,
    SubscriptionStart DATE,
    SubscriptionEnd DATE
);
INSERT INTO Subscriptions (SubscriptionID, UserID, SubscriptionStart, SubscriptionEnd)
VALUES
    (1, 101, '2023-01-01', '2023-03-31'),
    (2, 101, '2023-04-15', '2023-06-30'),
    (3, 102, '2023-02-01', '2023-04-30'),
    (4, 103, '2023-03-10', '2023-05-10'),
    (5, 101, '2023-06-01', '2023-08-31'),
    (6, 102, '2023-04-01', '2023-06-15');
6 rows affected

SELECT * FROM Subscriptions;


SubscriptionID
UserID
SubscriptionStart
SubscriptionEnd


1
101
2023-01-01
2023-03-31

2
101
2023-04-15
2023-06-30

3
102
2023-02-01
2023-04-30

4
103
2023-03-10
2023-05-10

5
101
2023-06-01
2023-08-31

6
102
2023-04-01
2023-06-15


SELECT s1.UserID
	,s1.SubscriptionID AS SubID
	,s1.SubscriptionStart AS SubStart
	,s1.SubscriptionEnd AS SubEnd
	,s2.UserID AS OverlapUserID
	,s2.SubscriptionID AS OverlapSubID
	,s2.SubscriptionStart AS OverlapSubStart
	,s2.SubscriptionEnd AS OverlapSubEnd
FROM Subscriptions s1
JOIN Subscriptions s2 ON s1.UserID <> s2.UserID
	AND s1.SubscriptionID < s2.SubscriptionID
	AND s1.SubscriptionStart <= s2.SubscriptionEnd
	AND s1.SubscriptionEnd >= s2.SubscriptionStart
ORDER BY s1.UserID
	,s1.SubscriptionID;


UserID
SubID
SubStart
SubEnd
OverlapUserID
OverlapSubID
OverlapSubStart
OverlapSubEnd


101
1
2023-01-01
2023-03-31
102
3
2023-02-01
2023-04-30

101
1
2023-01-01
2023-03-31
103
4
2023-03-10
2023-05-10

101
2
2023-04-15
2023-06-30
102
3
2023-02-01
2023-04-30

101
2
2023-04-15
2023-06-30
103
4
2023-03-10
2023-05-10

101
2
2023-04-15
2023-06-30
102
6
2023-04-01
2023-06-15

101
5
2023-06-01
2023-08-31
102
6
2023-04-01
2023-06-15

102
3
2023-02-01
2023-04-30
103
4
2023-03-10
2023-05-10

103
4
2023-03-10
2023-05-10
102
6
2023-04-01
2023-06-15


SELECT s1.UserID
	,s1.SubscriptionID AS SubID
	,s1.SubscriptionStart AS SubStart
	,s1.SubscriptionEnd AS SubEnd
	,s2.UserID AS OverlapUserID
	,s2.SubscriptionID AS OverlapSubID
	,s2.SubscriptionStart AS OverlapSubStart
	,s2.SubscriptionEnd AS OverlapSubEnd
FROM Subscriptions s1
JOIN Subscriptions s2 ON s1.UserID = s2.UserID
	AND s1.SubscriptionID < s2.SubscriptionID
	AND s1.SubscriptionStart <= s2.SubscriptionEnd
	AND s1.SubscriptionEnd >= s2.SubscriptionStart
ORDER BY s1.UserID
	,s1.SubscriptionID;


UserID
SubID
SubStart
SubEnd
OverlapUserID
OverlapSubID
OverlapSubStart
OverlapSubEnd


101
2
2023-04-15
2023-06-30
101
5
2023-06-01
2023-08-31

102
3
2023-02-01
2023-04-30
102
6
2023-04-01
2023-06-15

SubscriptionID	UserID	SubscriptionStart	SubscriptionEnd
1	101	2023-01-01	2023-03-31
2	101	2023-04-15	2023-06-30
3	102	2023-02-01	2023-04-30
4	103	2023-03-10	2023-05-10
5	101	2023-06-01	2023-08-31
6	102	2023-04-01	2023-06-15