WITH: Name the Steps of a Query

A CTE (Common Table Expression) gives a name to a query, so you can use it like a table in the next query. You write it with the WITH keyword.

CTEs make long queries easier to read. You build the answer in small steps, and each step has a name.


Syntax

WITH step_name AS (
  SELECT ...
)
SELECT ... FROM step_name;

Example 1: Departments with 4 or More Employees

WITH dept_counts AS (
  SELECT department, COUNT(*) AS staff
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_counts
WHERE staff >= 4
ORDER BY department;

Output:

 department | staff 
------------+-------
 Finance    |     4
 IT         |     6
 Sales      |     6
(3 rows)

The CTE dept_counts counts the employees in each department. Then the main query reads from dept_counts like a normal table, and keeps the departments with 4 or more employees.


Example 2: Several Steps

You can write more than one CTE. Put a comma between them, and a later step can use an earlier one:

WITH it_team AS (
  SELECT name, age FROM employees
  WHERE department = 'IT'
),
young_it AS (
  SELECT * FROM it_team WHERE age < 28
)
SELECT * FROM young_it ORDER BY age;

Output:

  name  | age 
--------+-----
 Mike   |  24
 Olivia |  25
 Emma   |  26
(3 rows)

Tip

A CTE exists only while its query runs. It is not saved like a table or a view.

In the exercise, use a CTE to find the departments with a high average age.