0

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: Example input below:

Desired Output:

enter image description here

1

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:

enter image description here

  • Hi Parvesh if my answer helped you will be great if you can accept and vote up as customery in SO – Tamir Klein May 14 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.