WITH

SQL · Unit 10: WITH and sets

Full plan
SQL
9 exercises

Last unit ended with an eighteen-line query. It looked for the customers who have spent more than the average, and the summary of what each one has spent was written twice, one of them two parentheses deep.

WITH gives a query a name before you start. The summary is written once, at the top, and it's called per_customer. What follows uses it like a table, both times it needs it. It returns what last unit's query did: Luke and Dylan. It isn't shorter. It has seventeen lines. What changed is that the summary is written once, and that the query reads from top to bottom.

WITH per_customer AS (
  SELECT c.id, c.name,
    SUM(p.price * o.quantity)
      AS money
  FROM orders o
  JOIN customers c
    ON o.customer_id = c.id
  JOIN products p
    ON o.product_id = p.id
  GROUP BY c.id, c.name
)
SELECT name
FROM per_customer
WHERE money > (
  SELECT AVG(money)
  FROM per_customer
);

Returns

name
Luke
Dylan

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
The owner decides accessories shouldn't count in this list, so the summary needs a WHERE that leaves out the Accessories category. In how many places do you write it in each version?
2. Put the lines in order
Among customers who have bought something, how many orders has each one placed, on average? Put in order the query that first counts each customer's orders and then averages those counts. Two lines don't belong.
3. Predict the output
Ann, customer 1, and Luke, customer 2, have three orders each. Ann's are for 2, 1, and 1 units, and Luke's for 5, 2, and 1. Read the query from top to bottom. What does it return for the two of them?
4. Find the bug
On which days did more than one order come in? Here SQL answers near ";": syntax error. Which line has the problem?
5. Multiple choice
A coworker moves a subquery from the FROM into a WITH, without touching anything else, and says the report runs faster that way. Is that right?
6. Find the bug
How many units has each customer received in big boxes? Only three customers have received any, and the query returns six rows. Ann, customer 1, shows up with 4 units, and none of her orders is over 2. Which line has the problem?
7. Complete the code
Which products have had more units ordered than the average product? The step is used twice, once to list and once, inside the parentheses, to get the average. Fill in the blanks in the two FROMs.
8. Predict the output
A step can be joined too, and then it takes an alias like any table. pc is per_customer. Three customers live in Denver. Ann has three orders, Valerie has two, and Brian hasn't ordered anything. What does this query return?
9. Write the code
Who buys more, younger customers or older ones? The step already adds up each customer's units. Below it, write the query that returns a single number: the average units per customer among those under 30 who have ordered something, rounded to 2 decimals. Age is in customers. Join the step with the alias pc.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →