For the table:

I need to return for each user_id the start date and end date for each phase when a contains all the phases in the middle and b is the end phase, without using window functions. Should look like this:

CodePudding user response：

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

result

CodePudding user response：

Without using window functions.
You can do a self-join to the table.
Then calculate a rank/group for the phases.
Then it's simple to group by the user and the phase group.

select user_id
, min([date]) as start_date
, max([date]) as end_date
from
(
  select t1.user_id, t1.[date], t1.phase
  , count(t2.phase) as grp
  from your_table t1
  left join your_table t2
    on t2.user_id = t1.user_id
   and t2.phase = 'b'
   and t2.[date] >= t1.[date]
  group by t1.user_id, t1.[date], t1.phase
) q
group by user_id, grp
order by user_id, start_date

Test on db<>fiddle here