Eliminate correlated subqueries

> Error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.' 

https://datawise.dev/using-correlated-subqueries-in-bigquery