By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tb(date date, phase char(1),user_id int);
insert into tb values
('1.1.20', 'a', 10),
('2.1.20', 'a', 10),
('3.1.20', 'b', 10),
('4.1.20', 'a', 10),
('5.1.20', 'a', 10),
('6.1.20', 'b', 10);
6 rows affected
with cte1 as(
select user_id,date,
case
when phase = 'a' and (lag(phase)over(order by date) is null or lag(phase)over(order by date) = 'b')
then 1
else 0
end grp
from tb),
cte2 as(
select user_id,date, sum(grp)over(order by date) as rnk
from cte1)
select user_id,min(date)start_date,max(date)end_date
from cte2
group by user_id,rnk
| user_id | start_date | end_date |
|---|---|---|
| 10 | 2020-01-01 | 2020-03-01 |
| 10 | 2020-04-01 | 2020-06-01 |
with cte1 as(
select *,
case
when phase = 'b' then 'end'
when phase = 'a' and
(select top 1 phase from tb t2 where t2.date < t1.date order by date desc) = 'a'
then 'no'
else 'start' end grp
from tb t1),
cte2 as(
select *,
(select top 1 date from cte1 t2 where t2.phase = 'a' and t2.date = t1.date)start_date,
(select top 1 date from cte1 t2 where t2.phase = 'b' and t2.date = t1.date)end_date
from cte1 t1
where grp = 'start' or grp = 'end'),
cte3 as(
select *
from cte2
where start_date is not null
),
cte4 as(
select *
from cte2
where end_date is not null
)
select t1.user_id,t1.start_date,t2.end_date
from cte3 t1
cross apply
(select top 1 *
from cte4 t2
where t1.date < t2.date) t2
| user_id | start_date | end_date |
|---|---|---|
| 10 | 2020-01-01 | 2020-03-01 |
| 10 | 2020-04-01 | 2020-06-01 |