There are a lot of comments about de error on CTEs and in-memory, and I will ignore this point.
About joins and subqueries (a CTE is a subquery):
For these queries, you can add your where to the join. This way, the database will do the filtering before the join. Doing this, you can achieve the same performance with less code.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.