title: Merge Overlapping Intervals and Track Maximum Value in BigQuery SQL
body: <p>I am trying to solve a problem where i want to merge overlapping intervals for a given column id, but i also want to track the maximum value for each overlapped interval. I have start_time and stop_time for each interval and each interval has a hierarchy/priority associated with it.</p>
  
  <p>These are the following columns in the table:
  id, start_time, stop_time, some_value</p>
  
  <p>example input:</p>
  
  <p><a href="https://i.stack.imgur.com/zoicr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zoicr.png" alt="enter image description here"></a></p>
  
  <p>example output:</p>
  
  <p><a href="https://i.stack.imgur.com/IgpJf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IgpJf.png" alt="enter image description here"></a></p>