By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (
USER_ID int , CONTRACT_ID int, START_DATE date, END_DATE date);
insert into t values
(1 , 14 , '2021-02-18','2022-04-18'),
(1 , 13 , '2019-01-01','2020-01-01'),
(1 , 12 , '2018-01-01','2019-01-01'),
(1 , 11 , '2017-02-13','2019-02-13'),
(2 , 23 , '2021-06-19','2022-04-18'),
(2 , 22 , '2019-07-01','2020-07-01'),
(2 , 21 , '2019-01-19','2020-01-19');
/*
+---------+------------+------------+
| 1 | 18.02.2021 | 18.04.2022 |
| 1 | 13.02.2017 | 01.01.2020 |
| 2 | 19.06.2021 | 18.04.2022 |
| 2 | 19.01.2019 | 01.07.2020 */
7 rows affected
; with extendedset as
(
select
A.user_id,
A.contract_id,
A.start_date,
max(case when B.user_id is null then A.end_date else B.end_date end) end_date
from t A left join t B
on
A.user_id=B.user_id
and A.contract_id<>B.contract_id
and A.end_date>=B.start_date
and A.end_date <=B.end_date
group by A.user_id,
A.contract_id,
A.start_date
)
select user_id,start_date, end_date from
extendedset
t1
where not exists
(select 1 from extendedset t2
where t1.user_id=t2.user_id and t1.contract_id<>t2.contract_id
and t1.start_date >= t2.start_date and t1.end_date <=t2.end_date)
| user_id | start_date | end_date |
|---|---|---|
| 1 | 2017-02-13 | 2020-01-01 |
| 1 | 2021-02-18 | 2022-04-18 |
| 2 | 2019-01-19 | 2020-07-01 |
| 2 | 2021-06-19 | 2022-04-18 |