Subqueries and CTEs

Answer in steps

"Dishes priced above average" needs the average first. A subquery computes it inside the main query. For multi-step questions, a CTE (WITH name AS (…)) names each step so the query reads top to bottom.

Subqueries and WITH

SELECT name, price FROM dishes
WHERE price > (SELECT avg(price) FROM dishes)
ORDER BY price;

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

WITH order_totals AS (
  SELECT o.id, o.customer_id, sum(d.price * oi.qty) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN dishes d ON d.id = oi.dish_id
  WHERE o.status = 'served'
  GROUP BY o.id
)
SELECT c.name, count(*) AS orders, sum(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
GROUP BY c.name
ORDER BY spent DESC;

Answer in steps

Subqueries and WITH

The rest of this lesson