CODEWITHSUNDEEP
sqljoingoogle-bigquery
Awssylearn
Awssylearn

Reputation: 55

Join in biqquery with recent timestamp

I have 2 tables in bigquery as below. I would like to select some columns by joining (left join) both the tables however I like the 'activity' column to be fetched from second table with the latest timestamp (top row based on latest time).

userId  age height
abc 34  160
xyz 32  170

userId  activity    time
abc running         10:45:00
abc walking         10:20:00
abc relax           10:10:00

Output should be like below

userId  age activity    time
abc 34  running         10:45:00
xyz 32  null            null

Please suggest the sql code in bigquery.

Thanks

Tried left join with row_num

Upvotes: 0

Views: 59

Answers (1)

Jaytiger
Jaytiger

Reputation: 12244

You can consider below.

-- sample data
WITH users AS (
  SELECT 'abc' userId, 34 age, 160 height UNION ALL
  SELECT 'xyz', 32, 170
),
activities AS (
  SELECT 'abc' userId, 'running' activity, TIME '10:45:00' time UNION ALL
  SELECT 'abc', 'walking', '10:20:00' UNION ALL
  SELECT 'abc', 'relax', '10:10:00'
)
-- query starts here
SELECT userId, age, activity.* 
  FROM users u 
  LEFT JOIN (
    SELECT userId, ANY_VALUE(STRUCT(activity, time) HAVING MAX time) activity
      FROM activities
     GROUP BY userId
  ) a USING (userId);

query results

enter image description here

Upvotes: 1

Related Questions