CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT,
    start_date DATE,
    end_date DATE,
    amount DECIMAL(10,2)
);
INSERT INTO subscriptions
    (subscription_id, customer_id, start_date, end_date, amount)
VALUES
-- Customer 101: overlapping subscriptions
(1, 101, '2026-01-01', '2026-03-31', 300.00),
(2, 101, '2026-03-15', '2026-05-31', 250.00),

-- Customer 102: no overlap; periods are consecutive
(3, 102, '2026-01-01', '2026-01-31', 100.00),
(4, 102, '2026-02-01', '2026-02-28', 100.00),

-- Customer 103: nested subscription period
(5, 103, '2026-01-01', '2026-06-30', 600.00),
(6, 103, '2026-02-01', '2026-02-28', 100.00),
(7, 103, '2026-04-01', '2026-07-31', 400.00),

-- Customer 104: same-day boundary overlap
(8, 104, '2026-05-01', '2026-05-31', 100.00),
(9, 104, '2026-05-31', '2026-06-30', 100.00),

-- Customer 105: multiple non-overlapping subscriptions
(10, 105, '2026-01-01', '2026-01-15', 50.00),
(11, 105, '2026-02-01', '2026-02-15', 50.00),
(12, 105, '2026-03-01', '2026-03-15', 50.00);



SELECT DISTINCT
    s1.customer_id,
    s1.subscription_id,
    s1.start_date,
    s1.end_date
FROM subscriptions s1
JOIN subscriptions s2
    ON s1.customer_id = s2.customer_id
    AND s1.subscription_id <> s2.subscription_id
    AND s1.start_date <= s2.end_date
    AND s1.end_date >= s2.start_date
ORDER BY s1.customer_id, s1.start_date;