CTEs with WITH

A CTE (Common Table Expression) lets you name a complex query result and treat it like a temporary table for the rest of your query - so you can build logic in clean, readable steps instead of cramming everything into one tangled nest.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Breaking Retail Queries into Steps with CTEs

WITH customer_totals AS (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_revenue
FROM customer_totals
WHERE total_revenue > 100
ORDER BY total_revenue DESC

Result:

customer_id	total_revenue
C103	210.75
C101	210.49

Step by step

WITH lets you name a sub-query so the main query can reference it.

WITH customer_totals AS (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY customer_id
)

After SELECT, list the column names you want, separated by commas.

WITH customer_totals AS (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_revenue

FROM tells SQL which table to pull the data from.

WITH customer_totals AS (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_revenue
FROM customer_totals

Practice

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

1. Using orders, write a CTE called totals that calculates SUM(revenue) per customer_id, then SELECT * from the CTE.

WITH totals AS (SELECT customer_id, SUM(revenue) FROM orders GROUP BY customer_id) SELECT * FROM totals

2. Using customers, write a CTE called totals that calculates SUM(email) per full_name, then SELECT * from the CTE.

WITH totals AS (SELECT full_name, SUM(email) FROM customers GROUP BY full_name) SELECT * FROM totals

3. Using products, write a CTE called totals that calculates SUM(price) per brand, then SELECT * from the CTE.

WITH totals AS (SELECT brand, SUM(price) FROM products GROUP BY brand) SELECT * FROM totals

4. Using store_sales, write a CTE called totals that calculates SUM(revenue) per store_id, then SELECT * from the CTE.

WITH totals AS (SELECT store_id, SUM(revenue) FROM store_sales GROUP BY store_id) SELECT * FROM totals

5. Using employees, write a CTE called totals that calculates SUM(salary) per department, then SELECT * from the CTE.

WITH totals AS (SELECT department, SUM(salary) FROM employees GROUP BY department) SELECT * FROM totals

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