Find min and max for subsets of consecutive rows - gaps and islands

Trying to build a query.

The input is ordered by rownumber in column 'rn' starting with 1 for each unique value in 'name' and defining a given sequence of entries in 'act'. In column 'act' it holds two values in multiple occurence, >sleep< and >wake<. The goal is to find for each consecutive set of rows of one of those values the minimum and maximum value of startt and endd.

The source of the input does not provide further columns. The number of members in each subset can be very much higher then in this example.

I tried different ways of aggregating, but none worked. I believe using

LEAD

and

LAGG

and further trickery might get me there, but that appears to be awfully unelegant. I have the notion it is key to differentiate each subset, i.e. create an identifier unique to all its members. With this at hand an aggregate with

; --- all rows, no aggregating
with
cte as (
select
name
,act
,row_number() over (partition by name order by name,startt) rn
,row_number() over (partition by name, act order by name,startt) act_n
,startt
,endd
from
@t )
select
name
,act
,startt
,endd
,rn
,act_n
,rn - act_n diff
from
cte
order by
name
,rn

;--- aggregating for the desired ouput
with
cte as (
select
name
,act
,row_number() over (partition by name order by name,startt) rn
,row_number() over (partition by name, act order by name,startt) act_n
,startt
,endd
from
@t )
select
name
,act
,min(startt) startt
,max(endd) endd
,min(rn) min_rn
,max(rn) max_rn
from
cte
group by
name
,act
,rn - act_n
order by
name
,min(rn)

You want to find consecutive groups of similar rows and then aggregation. I like the difference of row numbers approach:

select name, act, min(startt) as startt, max(endd) as endd
from (select i.*,
row_number() over (partition by name, act order by rn) as seqnum_na,
row_number() over (partition by name order by rn) as seqnum_n
from input i
) i
group by (seqnum_n - seqnum_na), name, act;