How to use SQL CTE's - almost like variables in SQL

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;