Common Table Expression (Cte)

Basic CTTE

Using CTE we can macro our query. For example

postgres@localhost:dvdrental> with action_films as (select title, length from film) select * from action_films limit 10;
+-------------------+--------+
| title             | length |
|-------------------+--------|
| Chamber Italian   | 117    |
| Grosse Wonderful  | 49     |
| Airport Pollock   | 54     |
| Bright Encounters | 73     |
| Academy Dinosaur  | 86     |
| Ace Goldfinger    | 48     |
| Adaptation Holes  | 50     |
| Affair Prejudice  | 117    |
| African Egg       | 130    |
| Agent Truman      | 169    |
+-------------------+--------+
SELECT 10
Time: 0.008s

We can use for more complicated query for example we can use one of the complicated query as res and query based on res

postgres@localhost:dvdrental> with res as (
   select staff_id, count(rental_id) rental_count from rental group by staff_id
   ) select s.staff_id, first_name, last_name, rental_count from staff s inner join res using(staff_id); 
+----------+------------+-----------+--------------+
| staff_id | first_name | last_name | rental_count |
|----------+------------+-----------+--------------|
| 1        | Mike       | Hillyer   | 8040         |
| 2        | Jon        | Stephens  | 8004         |
+----------+------------+-----------+--------------+
SELECT 2
Time: 0.022s

Or we can combine multiple ctes

WITH film_stats AS (
    -- CTE 1: Calculate film statistics
    SELECT
        AVG(rental_rate) AS avg_rental_rate,
        MAX(length) AS max_length,
        MIN(length) AS min_length
    FROM film
),
customer_stats AS (
    -- CTE 2: Calculate customer statistics
    SELECT
        COUNT(DISTINCT customer_id) AS total_customers,
        SUM(amount) AS total_payments
    FROM payment
)
-- Main query using the CTEs
SELECT
    ROUND((SELECT avg_rental_rate FROM film_stats), 2) AS avg_film_rental_rate,
    (SELECT max_length FROM film_stats) AS max_film_length,
    (SELECT min_length FROM film_stats) AS min_film_length,
    (SELECT total_customers FROM customer_stats) AS total_customers,
    (SELECT total_payments FROM customer_stats) AS total_payments;

Recursion

We can use recursive CTE

WITH RECURSIVE cte_name (column1, column2, ...)
AS(
    -- anchor member (base case)
    SELECT select_list FROM table1 WHERE condition
    UNION [ALL]
    -- recursive term (later case query based on base case)
    SELECT select_list FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;

For example given this table

postgres@localhost:dvdrental> select * from employees;
+-------------+-------------------+------------+
| employee_id | full_name         | manager_id |
|-------------+-------------------+------------|
| 1           | Michael North     | <null>     |
| 2           | Megan Berry       | 1          |
| 3           | Sarah Berry       | 1          |
| 4           | Zoe Black         | 1          |
| 5           | Tim James         | 1          |
| 6           | Bella Tucker      | 2          |
| 7           | Ryan Metcalfe     | 2          |
| 8           | Max Mills         | 2          |
| 9           | Benjamin Glover   | 2          |
| 10          | Carolyn Henderson | 3          |
| 11          | Nicola Kelly      | 3          |
| 12          | Alexandra Climo   | 3          |
| 13          | Dominic King      | 3          |
| 14          | Leonard Gray      | 4          |
| 15          | Eric Rampling     | 4          |
| 16          | Piers Paige       | 7          |
| 17          | Ryan Henderson    | 7          |
| 18          | Frank Tucker      | 8          |
| 19          | Nathan Ferguson   | 8          |
| 20          | Kevin Rampling    | 8          |
+-------------+-------------------+------------+

Executing this query

postgres@localhost:dvdrental> with recursive subordinates as (
 select employee_id, manager_id, full_name from employees where employee_id = 2
 union
 select e.employee_id, e.manager_id, e.full_name from employees e inner join subordinates s on s.employee_id = e.manager_id)
 select * from subordinates;
+-------------+------------+-----------------+
| employee_id | manager_id | full_name       |
|-------------+------------+-----------------|
| 2           | 1          | Megan Berry     |
| 6           | 2          | Bella Tucker    |
| 7           | 2          | Ryan Metcalfe   |
| 8           | 2          | Max Mills       |
| 9           | 2          | Benjamin Glover |
| 16          | 7          | Piers Paige     |
| 17          | 7          | Ryan Henderson  |
| 18          | 8          | Frank Tucker    |
| 19          | 8          | Nathan Ferguson |
| 20          | 8          | Kevin Rampling  |
+-------------+------------+-----------------+
SELECT 10
Time: 0.009s
postgres@localhost:dvdrental>

How it works?

We run the first query and get the base case select employee_id, manager_id, full_name from employees where employee_id = 2

employee_id | manager_id |  full_name
-------------+------------+-------------
           2 |          1 | Megan Berry

we do a select e.employee_id, e.manager_id, e.full_name from employees e inner join subordinates s on s.employee_id = e.manager_id) where subordinates s is the base case above

employee_id | manager_id |    full_name
-------------+------------+-----------------
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover

And then we keep on continuing