Gotcha! That makes sense.
I found this older request that seems to be asking for something similar as well: Do join/Union/CTE statement over multiple table for advanced analytics
And this question Need suggestion in analyzing data from multiple tables that would be solved by your request too.
Grouping to help gather all the relevant information 