Comment by fifilura

Of course there are optimizations to be made, such as not joining on the raw data or saving the order by to last. And avoid outer joins between two large sized partitioned tables.

But to me those optimizations are not imperative in nature.

(And BQ will probable eat the 10 million to 10 million join for breakfast...)