By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.22 |
create table t(household_id int, tv_set_id int, start_time int, end_time int)
insert into t
select '111',1,500 ,550 union all
select '111',1,550 ,570 union all
select '111',1,590 ,620 union all
select '111',1,650 ,670 union all
select '111',2,660 ,680 union all
select '111',2,680 ,700 union all
select '111',2,700 ,750 union all
select '111',2,750 ,770 union all
select '112',2,1050,1060 union all
select '113',1,1060,1080 union all
select '113',1,1080,1100 union all
select '113',1,1100,1120 union all
select '113',1,1500,1520
with data
as (
select *
,case when lag(end_time) over(partition by household_id,tv_set_id order by end_time)
<> start_time then
sum(1) over(partition by household_id,tv_set_id order by end_time)
else
sum(0) over(partition by household_id,tv_set_id order by end_time)
end as group_number
from t
)
select household_id
,tv_set_id
,min(start_time) as start_time
,max(end_time) as end_time
from data
group by household_id,tv_set_id,group_number
| household_id | tv_set_id | start_time | end_time |
|---|---|---|---|
| 111 | 1 | 500 | 570 |
| 111 | 1 | 590 | 620 |
| 111 | 1 | 650 | 670 |
| 111 | 2 | 660 | 770 |
| 112 | 2 | 1050 | 1060 |
| 113 | 1 | 1060 | 1120 |
| 113 | 1 | 1500 | 1520 |