Level 3 · Customer lifetime value

A common table expression (CTE) is a named subquery you define at the top with WITH, then use like a table:

WITH order_revenue AS (
  SELECT o.order_id, o.customer_id,
         SUM(i.quantity * i.unit_price * (1 - i.discount_pct / 100.0)) AS revenue
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'completed' AND i.quantity > 0
  GROUP BY o.order_id, o.customer_id
)
SELECT customer_id, COUNT(*) AS orders, SUM(revenue) AS lifetime_revenue
FROM order_revenue
GROUP BY customer_id;

It does the same job as a derived table in FROM, but reads top to bottom, the way you would explain the logic out loud: “first get revenue per order, then roll it up per customer.”

Why average order value needs two steps

Average order value (AOV) is the average of order totals. If you computed AVG(quantity * unit_price ...) directly on the joined lines, you would get the average line value, a much smaller number. The CTE fixes the grain at one row per order, so AVG(revenue) is a real AOV. This is the most useful thing CTEs do for analysts: make each step’s grain explicit.

Several CTEs

Separate them with commas. Later ones can read earlier ones:

WITH order_revenue AS (
  ...
),
customer_value AS (
  SELECT customer_id, SUM(revenue) AS lifetime_revenue
  FROM order_revenue
  GROUP BY customer_id
)
SELECT *
FROM customer_value
WHERE lifetime_revenue > 10000;

Name CTEs after what one row represents (order_revenue, customer_value, monthly_sales). Then anybody can read the query’s plan from the names alone.

Debugging with CTEs

While developing, end the query with SELECT * FROM order_revenue LIMIT 20 to look at any step in isolation. That is what the starter does. Check the row count of each step against what you expect (here, one row per completed order) before building on it.

Dialect notes

CTEs are supported in Postgres, DuckDB, BigQuery, Snowflake, SQL Server and MySQL 8+ (not MySQL 5.7). Postgres before version 12 always materialized CTEs, which could make them slower than subqueries; in current versions the optimizer inlines them, as DuckDB and BigQuery do. WITH RECURSIVE is a separate feature for walking hierarchies.

Interview note

In live SQL interviews, structuring your answer as CTEs is almost always a good move. It shows your thinking, makes it easy for the interviewer to follow, and lets you test each step. A long query with three levels of nested subqueries works too, but it is much harder to explain under pressure.