Module lessons (1/3)
Introduction to CTEs (WITH)
Introduction to CTEs (WITH)
Sooner or later it happens to everyone: you need to filter data based on the result of an aggregation, then take that filter and JOIN it with another aggregated query. You start writing a SELECT inside a SELECT, which in turn ends up in the FROM of yet another SELECT.
The result? Spaghetti SQL, a query that's unreadable for any human being.
CTEs (Common Table Expressions) let you untangle this mess. You activate them with the WITH keyword, which allows you to define temporary named queries and then reuse them as "virtual tables" in the final query.
Basic Syntax
WITH premium_customers AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
)
SELECT customers.first_name, premium_customers.total_spent
FROM customers
JOIN premium_customers ON customers.id = premium_customers.customer_id;As you can see, the main query at the bottom reads from premium_customers as if it were a real table! And it's much more readable than a mega inner-join with nested subqueries.
We want the very expensive products. Use a CTE called 'luxury_products' to select 'id' and 'price' from 'products' where 'price > 500'. Then run a main query on this CTE and show everything (*).
Show hint
Start with WITH luxury_products AS (...) then SELECT * FROM luxury_products.
Solution available after 3 attempts
We want to find users who write reviews. Create a CTE 'reviewers' that extracts the unique (DISTINCT) 'customer_id' values from 'reviews'. Then extract 'id', 'first_name', 'last_name' from the 'customers' table and \`JOIN\` it with the 'reviewers' CTE on id.
Show hint
WITH reviewers AS (SELECT DISTINCT customer_id FROM reviews) SELECT id, first_name, last_name FROM customers JOIN reviewers ON customers.id = reviewers.customer_id;
Solution available after 3 attempts