Eliminate correlated subqueries

[dovydasm]

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