Today’s topic is a general primer on something that is called “local-global aggregation”. Effectively, this is a technique to allow you to take a query containing joins and group bys and perform the group by partially “before” the joins. This can dramatically reduce the number of rows that a query has to process to return a result, and therefore it is a very powerful operation. This technique is also quite useful to split a grouping operation into something that can be done in parallel. That obviously can also speed your query as well.
I hope to put something more formal together soon, work permitting, but here is an introduction that you can read in the meantime: