Thanks to subqueries, you already know how to count badges per trainer with a derived table — a query tucked inside the FROM:
↗️
Loading editor...
↶
↷
A CTE — Common Table Expression — does exactly the same thing, but the inner query is written first and given a name with WITH. The main query then reads like a sentence:
↗️
Loading editor...
↶
↷
WITH per_trainer AS (…) defines a temporary, named result. It exists only for this statement, and the main query uses per_trainer like any table.
Same engine, different reading order
Nothing changes for the database — a CTE and a derived table produce the same plan. The difference is for humans: the building blocks come first, the conclusion last, and nothing is nested three levels deep.