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');
Advertisement
Answer
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
