CTEs

01The problem

The finance director asks for something that sounds simple:

"How much does each customer still owe us?"

What a customer owes is what we charged for delivered shipments, minus what they've paid. Charges live in shipments; payments live in payments, and one shipment can have several payments. Getting this right takes several steps, and doing it in one tangled query is how analysts end up with wrong numbers.

02The concept

A CTE (common table expression) is a named, temporary result you define at the top of a query with WITH, and then use like a table.

WITH step_one AS (
  SELECT …
),
step_two AS (
  SELECT … FROM step_one …
)
SELECT … FROM step_two;

A CTE does the same job as a subquery in FROM, but you read it top to bottom, like a recipe, and you can use each step more than once. CTEs only exist while the query runs.

03Example

WITH charged AS (
  SELECT customer_id, SUM(freight_charge) AS total_charged
  FROM shipments
  WHERE status = 'Delivered'
  GROUP BY customer_id
),
paid AS (
  SELECT s.customer_id, SUM(p.amount) AS total_paid
  FROM payments AS p
  JOIN shipments AS s ON s.shipment_id = p.shipment_id
  GROUP BY s.customer_id
)
SELECT
  c.company_name,
  ch.total_charged,
  COALESCE(pd.total_paid, 0)                    AS total_paid,
  ch.total_charged - COALESCE(pd.total_paid, 0) AS balance_owed
FROM charged AS ch
JOIN customers AS c ON c.customer_id = ch.customer_id
LEFT JOIN paid AS pd ON pd.customer_id = ch.customer_id
ORDER BY balance_owed DESC
LIMIT 10;

04Walkthrough

1. charged adds up the charges for delivered shipments, one row per customer.
2. paid adds up payments, one row per customer. Payments don't store the customer, so we join to shipments to find it.
3. The final query joins the two summaries to customers and subtracts.
4. LEFT JOIN paid keeps customers who haven't paid anything, and COALESCE(pd.total_paid, 0) turns their NULL into 0 so the subtraction works.

Why two separate summaries? If you joined shipments and payments first and then summed, every shipment with two payments would have its charge counted twice. Aggregating each table on its own and then joining the totals avoids that double-counting. It's one of the most common mistakes in real reports.

05Practice

06Challenge

07Check your understanding