Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.
When SQL Server gets that process wrong, switch to temp tables.
Let’s start with an example. I’m using SQL Server 2025 and the big Stack Overflow 2024-04 database in 2025 compat level.
The below query says, “Find the most popular Users.Location – the one