Continuous subset and ranking using sql-CodePudding

I have a dataset like below:

Now, I need the output as below:

start_time  end_time    count
10:01   10:04   3
10:05   10:07   2

For this purpose, I wrote a query but it is not giving me the desired sequence. My query is as below:

with on_off as 
(
select  time,status,case when status!=lag(status) over(order by time) then 1 else 0 end as continuous_count
  from time_status
)
,
grp as 
(
  select *, row_number() over(partition by continuous_count order by time) rnk from  on_off
)
select * from grp order by time

It generates the output as below:

But in the rank section I need something as below:

So, what exactly am I doing wrong here?

Here are the PostgresSQL DDLs:

create table time_status(time varchar(10)  null, status varchar(10) null);

INSERT into  time_status(time,status) values('10:01','ON');
INSERT into  time_status(time,status) values('10:02','ON');
INSERT into  time_status(time,status) values('10:03','ON');
INSERT into  time_status(time,status) values('10:04','OFF');
INSERT into  time_status(time,status) values('10:05','ON');
INSERT into  time_status(time,status) values('10:06','ON');
INSERT into  time_status(time,status) values('10:07','OFF');

CodePudding user response：

Try this query:

SELECT min(time) as start_time,
       max(time) as end_time,
       sum(case when status = 'ON' then 1 else 0 end) as cnt
  FROM (SELECT time, status,
               sum(case when status = 'OFF' then 1 else 0 end)
                 over (order by time desc) as grp
          FROM time_status) _
 GROUP BY grp
 ORDER BY min(time);

->Fiddle