Resolved: Join tables in bigquery

activity    activity_date   activity_time       userID  value
walking     2023-03-11      2023-03-11 14:00:00 abc     32
walking     2023-03-12      2023-03-12 14:01:00 abc     45

activity    activity_date   activity_time       userID  value
running     2023-03-11      2023-03-11 14:00:00 abc     12
running     2023-03-12      2023-03-12 14:01:00 abc     22

activity    activity_date   activity_time       userID  value
swimming    2023-03-11      2023-03-11 14:00:00 abc     56
swimming    2023-03-12      2023-03-12 14:01:00 abc     77
                
activity    activity_date   activity_time       userID  value
cycling     2023-03-11      2023-03-11 14:00:00 abc     54
cycling     2023-03-12      2023-03-12 14:01:00 abc     32
                
userID  age height  weight  
abc     43  170         98

activity_date   activity_time   userID  age height  weight  walking.value   running.value   swimming.value  cycling.value

2023-03-11  2023-03-11 14:00:00 abc 43  170 98  32  12  56  54
2023-03-12  2023-03-12 14:01:00 abc 43  170 98  45  22  77  32

SELECT 
  COALESCE(t1.activity_date, t2.activity_date) AS activity_date,
  COALESCE(t1.userID, t2.userID) AS userID,
  t1.walking_value,
  t1.swimming_value,
  t1.running_value,
  t2.cycling_value
FROM (
  SELECT 
    activity_date, 
    userID,
    MAX(CASE WHEN activity = 'walking' THEN value ELSE NULL END) AS walking_value,
    MAX(CASE WHEN activity = 'swimming' THEN value ELSE NULL END) AS swimming_value,
    MAX(CASE WHEN activity = 'running' THEN value ELSE NULL END) AS running_value
  FROM table1
  GROUP BY activity_date, userID
) t1
FULL OUTER JOIN (
  SELECT 
    activity_date, 
    userID,
    value AS cycling_value
  FROM table2
  WHERE activity = 'Cycling'
) t2
ON t1.activity_date = t2.activity_date AND t1.userID = t2.userID
ORDER BY activity_date, userID;

SELECT
  w.date,
  w.time,
  w.userID,
  w.value AS walking_value,
  r.value AS running_value
FROM
  `your_dataset.walking_table` AS w
JOIN
  `your_dataset.running_table` AS r
ON
  w.date = r.date
  AND w.time = r.time
  AND w.userID = r.userID;

SELECT
  w.date AS activity_date,
  w.time AS activity_time,
  u.userID,
  u.age,
  u.height,
  u.weight,
  w.value AS walking_value,
  r.value AS running_value,
  s.value AS swimming_value,
  c.value AS cycling_value
FROM
  `your_dataset.user_table` AS u
LEFT JOIN
  `your_dataset.walking_table` AS w
ON u.userID = w.userID
LEFT JOIN `your_dataset.running_table` AS r
ON u.userID = r.userID AND w.date = r.date AND w.time = r.time
LEFT JOIN `your_dataset.swimming_table` AS s
ON u.userID = s.userID AND w.date = s.date AND w.time = s.time
LEFT JOIN `your_dataset.cycling_table` AS c
ON u.userID = c.userID AND w.date = c.date AND w.time = c.time;