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.