Bigquery multiple unnest in a single select

Question

Following up on this - Bigquery combining repeated fields from 2 different tables

The above solution from @ElliottBrossard was what I was looking for. Thanks! I need to UNNEST the fields from above to get aggregations by student. Say, there is an additional field for cost (per course) For e.g. assuming the output from the above in a student_courses table, I will need to do something like,

SELECT
  COUNT(DISTINCT phone.number),
  COUNT(DISTINCT courses.Id),
  SUM(courses.Cost)
FROM
  student_courses,
  UNNEST(phone),
  UNNEST(courses)

My expected answer from the above is something like, 3, 4, $800 (assuming the 4 courses cost $200 each). The above query ends up acting like a cross join. Is it possible to get aggregate from multiple nests using a single Select?

Bigquery multiple unnest in a single select

1 Answers