Level 8 · Customer value bands

Some questions need several steps, each at a different grain. CTEs let you write them as a chain of named, readable steps instead of nested subqueries.

Recap: one CTE per step

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

Only one WITH, commas between the steps, and each step can read the ones above it. For this ticket:

1. customer_ltv: order lines summed per customer. One row per customer who completed an order.
2. banded: the same rows with a CASE label.
3. final SELECT: one row per band.

Run each step on its own while you build it. The starter shows the first ten rows of step one; check that the numbers look like money before you band them.

Reading the result

ltv_band	customers	avg_ltv	pct_of_revenue
under_500	468	214.80	1.7
500_to_1999	649	1,103.54	12.1
2000_to_9999	486	4,277.26	35.3
10000_plus	127	23,628.52	50.9

127 customers, about 7% of the 1,730 who completed an order, bring in half of all revenue. That is the kind of concentration finance and sales want to see: losing a handful of accounts in the top band would hurt more than losing all 468 in the bottom one.

The CASE conditions are checked from top to bottom and the first true one wins. That is why WHEN ltv < 2000 does not need ltv >= 500: anything below 500 was already labelled.

Common mistakes

• Banding at the wrong grain. A CASE on order lines bands lines, not customers.
• Dividing by the wrong total. SUM(ltv) inside the grouped query is the band’s own total. The grand total needs its own subquery on the CTE (or a window function, next chapter).
• Text bands that sort badly. ‘10000_plus’ sorts before ‘2000_to_9999’ alphabetically. If the order matters, add a numeric band_order column.

In SQL Server

CTEs are written the same way, but the statement before WITH must end with a semicolon, which is why you often see ;WITH in SQL Server code. CTEs cannot be followed by ORDER BY inside the CTE without TOP.