Question: I would like to combine product promotion start time and end time for the same product and time in range of start time and end time.
For example:
end time of ID1 and start time of ID2 is a continue time.
start time of ID3 is in the range of start and end time of ID2.
Therefore, ID1 ID2 ID3 should be combined together.

| ID | Product | Start Time | End Time |
| --- | --- | --- | --- |
| 1 | a | 2020-10-15 | 2020-10-20 |
| 2 | a | 2020-10-21 | 2020-10-24 |
| 3 | a | 2020-10-23 | 2020-10-25 |
| 4 | a | 2020-10-28 | 2020-10-29 |
| 5 | b | 2020-10-29 | 2020-10-31 |

Expected

| ID | Product | Start Time | End Time | Combine Count |
| --- | --- | --- | --- | --- |
| 1 | a | 2020-10-15 | 2020-10-25 | 3 |
| 4 | a | 2020-10-28 | 2020-10-29 | 1 |
| 5 | b | 2020-10-29 | 2020-10-31 | 1 |

Do you have any suggestion?

Answer: 
Consider below

```
select min(id) id,
    product,
    min(start_time) start_time,
    max(end_time) end_time,
    count(1) Combine_Count
from (
  select *, 
    countif(ifnull(isNew, true)) over win grp
  from (
    select *, 
      start_time > lag(end_time) over win + 1 isNew
    from `project.dataset.table`
    window win as (partition by product order by start_time)
  )
  window win as (partition by product order by start_time)
) t
group by product, grp    

```

when applied to sample data in your question - output is

[![enter image description here](https://i.stack.imgur.com/dvp8P.png)](https://i.stack.imgur.com/dvp8P.png)
Try this one:

```
WITH test AS (
  SELECT 1 AS ID, 'a' AS Product, DATE '2020-10-15' AS Start_Time, DATE '2020-10-20' AS End_Time UNION ALL
  SELECT 2, 'a', DATE '2020-10-21', DATE '2020-10-24' UNION ALL
  SELECT 3, 'a', DATE '2020-10-23', DATE '2020-10-25' UNION ALL
  SELECT 4, 'a', DATE '2020-10-28', DATE '2020-10-29' UNION ALL
  SELECT 5, 'b', DATE '2020-10-29', DATE '2020-10-31'
)
SELECT Product, min(Start_Time), max(End_time), count(*)
FROM (
  SELECT *, countif(is_new_range) OVER (PARTITION BY Product ORDER BY Start_Time) AS range_id
  FROM (
    SELECT *, DATE_DIFF(Start_Time, MAX(End_time) OVER (PARTITION BY Product ORDER BY Start_Time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), DAY) > 2 AS is_new_range
    FROM test
  )
)
GROUP BY Product, range_id

```

[![enter image description here](https://i.stack.imgur.com/dqWUY.png)](https://i.stack.imgur.com/dqWUY.png)