Here is a way to get this done

In the data block i create groups which is defined as any record whose previous end_time doenst match with my start_time and assign a group_number to it if its different, else i keep it same.

After that in the main block i group by this group_number, along with the household_id,tv_set_id to get the results.

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 |
+--------------+-----------+------------+----------+

db fiddle link https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ba5ade186ebc3cf693c505d863691670

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top