# CTEs and Subqueries — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/q-cte

> Name intermediate results.

## WITH for readability

A **common table expression** (`WITH name AS (...)`) names an intermediate result so complex queries read top to bottom. Subqueries can appear in WHERE (for example comparing to an average) or FROM. Since PostgreSQL 12, simple CTEs are inlined by the planner, so they no longer act as an optimisation barrier unless you write `MATERIALIZED`. Recursive CTEs (`WITH RECURSIVE`) walk hierarchies such as category trees.

## Orders above the average order value, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. A CTE computes per-order totals; the main query keeps orders above the average total, returning Meera's order (1,798) and Asha's second order (1,499).

```sql
WITH order_totals AS (
  SELECT o.id, o.customer_id, sum(oi.qty * p.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id
)
SELECT c.name, ot.id AS order_id, ot.total
FROM order_totals ot
JOIN customers c ON c.id = ot.customer_id
WHERE ot.total > (SELECT avg(total) FROM order_totals)
ORDER BY ot.total DESC;
```

Output:

```
 name  | order_id |  total  
-------+----------+---------
 Meera |        4 | 1798.00
 Asha  |        2 | 1499.00
(2 rows)
```

## Build queries in steps

Write each CTE, run it alone to check results, then add the next step.

**Quiz:** What is a CTE?

- [x] A named intermediate result defined with WITH
- [ ] A type of index
- [ ] A backup format
- [ ] A database user

*Answer:* A named intermediate result defined with WITH. Readable, composable queries.