sql · level 3 · unit 1 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
names a step so the rest of the query can use it.
Big questions are easier in steps. To find customers who spent over £50, first add up each customer's orders, then keep the big spenders. A subquery can do it, but queries nested inside queries soon get hard to read.
A , short for common table expression, gives a step a name. Write WITH spend AS ( ... ) before your main query, with any query inside the brackets. After that, spend works like a table, but only inside that one statement.
The main query then reads from the step. You read the whole thing top to bottom, like a recipe.
WITH spend AS (
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
)
SELECT c.name, s.spent
FROM spend AS s
JOIN customers AS c ON c.id = s.customer_id
WHERE s.spent > 50
ORDER BY s.spent DESC;Professionals name each step after what it holds, so spend beats t1.
guess first
Two statements, one CTE. What happens?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
WITH spend AS (
SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id
)
SELECT COUNT(*) AS customers FROM spend;
SELECT MAX(spent) AS top FROM spend;your turn
Your turn. Find the busy days.
Finish the CTE so daily holds one row per day with that day's takings. Then pick the amount that makes a day busy. The main query lists the busy days in order.
result
Your answer appears here as a table.
fix it
The big spenders query cannot find a column. Fix it!
result
Your answer appears here as a table.
- Read the error. Which column can the database not find?
spendonly has the columns its ownSELECTnames:customer_idandspent.- Change total to spent on line 7, then press run.
nailed it
you broke a big question into steps. :D
WITH name AS (...)names a step you can query like a table.- A CTE lasts for one statement only.
- A CTE has only the columns its
SELECTnames.
new words in your collection
- CTE
- WITH
This lesson isn’t saved. A free account remembers every lesson you finish.