CTEs allow us to create temporary tables in SQL. And use them as sort of variables in other queries.
Turns out this is one of the most important patterns to make your life easier when doing complicated queries.
WITH name_of_cte ( column1, column2, colmn3 ...) AS ( Query )
We assign a new name_of_cte table with columns - column2, column2, column3 etc. from any query.
WITH job_count (num_jobs, company_id) as ( SELECT company_id ,COUNT(*) AS num_jobs FROM jobs JOIN companies ON company_id = companies.id GROUP BY name) SELECT name, num_jobs FROM companies JOIN job_count on companies.id = company_id;