Two CTEs in a row

A CTE turns one hard query into two easy steps. Sometimes you need three: work something out, use it to work out something else, then answer. Put a comma between the CTEs, with no second WITH. The second CTE can read from the first.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Step one, step two, then the answer

WITH customer_totals AS (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY customer_id
), big_spenders AS (
  SELECT *
  FROM customer_totals
  WHERE total_revenue > 100
)
SELECT COUNT(*) AS big_spender_count
FROM big_spenders

Result:

big_spender_count
3

Step by step

The first step, as in the last level: each customer's total.

WITH customer_totals AS (SELECT customer_id, SUM(revenue) AS total_revenue FROM orders GROUP BY customer_id), big_spenders AS (SELECT * FROM customer_totals WHERE total_revenue > 100) SELECT COUNT(*) AS big_spender_count FROM big_spenders

No second WITH: a comma, then the next name. It reads from the first CTE, like a table.

WITH customer_totals AS (SELECT customer_id, SUM(revenue) AS total_revenue FROM orders GROUP BY customer_id), big_spenders AS (SELECT * FROM customer_totals WHERE total_revenue > 100) SELECT COUNT(*) AS big_spender_count FROM big_spenders

The main query counts the rows in big_spenders.

WITH customer_totals AS (SELECT customer_id, SUM(revenue) AS total_revenue FROM orders GROUP BY customer_id), big_spenders AS (SELECT * FROM customer_totals WHERE total_revenue > 100) SELECT COUNT(*) AS big_spender_count FROM big_spenders

Practice

Try each one in your head first, then open the answer.

1. Using orders, total each customer's revenue in a CTE called customer_totals, keep the customers whose total_revenue is over 100 in a second CTE called big_spenders, then count them as big_spender_count.

WITH customer_totals AS (SELECT customer_id, SUM(revenue) AS total_revenue FROM orders GROUP BY customer_id), big_spenders AS (SELECT * FROM customer_totals WHERE total_revenue > 100) SELECT COUNT(*) AS big_spender_count FROM big_spenders

2. Using orders, total each category's revenue in a CTE called category_totals, keep the categories whose total_revenue is over 150 in a second CTE called top_categories, then show them with the biggest first.

WITH category_totals AS (SELECT category, SUM(revenue) AS total_revenue FROM orders GROUP BY category), top_categories AS (SELECT * FROM category_totals WHERE total_revenue > 150) SELECT * FROM top_categories ORDER BY total_revenue DESC

3. Using orders, total each customer's revenue in a CTE called customer_totals, put the average of those totals in a second CTE called avg_total (name it avg_spend), then show customer_id and total_revenue for the customers above that average.

WITH customer_totals AS (SELECT customer_id, SUM(revenue) AS total_revenue FROM orders GROUP BY customer_id), avg_total AS (SELECT AVG(total_revenue) AS avg_spend FROM customer_totals) SELECT customer_id, total_revenue FROM customer_totals WHERE total_revenue > (SELECT avg_spend FROM avg_total)

4. Using store_sales, total each store's revenue in a CTE called store_totals, keep the stores whose total_revenue is over 300 in a second CTE called big_stores, then count them as big_store_count.

WITH store_totals AS (SELECT store_id, SUM(revenue) AS total_revenue FROM store_sales GROUP BY store_id), big_stores AS (SELECT * FROM store_totals WHERE total_revenue > 300) SELECT COUNT(*) AS big_store_count FROM big_stores

5. Using products, find each category's highest price in a CTE called top_prices, keep the categories whose max_price is under 100 in a second CTE called cheap_tops, then show them cheapest first.

WITH top_prices AS (SELECT category, MAX(price) AS max_price FROM products GROUP BY category), cheap_tops AS (SELECT * FROM top_prices WHERE max_price < 100) SELECT * FROM cheap_tops ORDER BY max_price

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed