Merge Overlapping intervals in Bigquery I have a bigquery table with following columns:
user_id, unique_id, start_timestamp(UTC), end_timestamp

unique_id is always unique, no repeating values. currently data is grouped by user_id. and then sorted by start_timestamp. 

Example Input:


Desired Output:
Since BigQuery support MIN/MAX aggregation function on time fields You can use this SQL:

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC


And this the sql with some test data you can play with

WITH `data` AS (
SELECT 1 AS user_id, 'a' as unique_id, timestamp('2019-01-20 5:10:05') as start_time, timestamp('2019-01-20 5:10:20') as end_time UNION ALL
SELECT 1 AS user_id, 'b' as unique_id, timestamp('2019-01-20 5:10:11') as start_time, timestamp('2019-01-20 5:15:31') as end_time 
)

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC


Which produce the following desired result: