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.
Syntax
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.
Usage example
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;