SQL CTE (WITH)

Readable queries with named subqueries.

✍️ 3 exercises + code challenge in this lesson

❮ Previous Next ❯

A Common Table Expression (CTE) gives a subquery a name with WITH, so a complex query can be read top to bottom:

WITH name AS (SELECT ...)
SELECT ... FROM name;

Several CTEs can be chained with commas. WITH RECURSIVE can walk through hierarchies and generate sequences.

Key Takeaways: SQL CTE (WITH)

Mastering SQL CTE (WITH) helps you write clean and reliable SQL code. Follow standard conventions, test edge cases, and keep logic simple. Try running the code example below and complete the exercises to build confidence.

SQL CTE (WITH) Code Example

SQL

WITH CustomerTotals AS (
  SELECT o.CustomerID, SUM(p.Price * i.Quantity) AS Spent
  FROM Orders o
  JOIN OrderItems i ON i.OrderID = o.OrderID
  JOIN Products p   ON p.ProductID = i.ProductID
  GROUP BY o.CustomerID
)
SELECT c.CustomerName, t.Spent
FROM CustomerTotals t
JOIN Customers c ON c.CustomerID = t.CustomerID
ORDER BY t.Spent DESC
LIMIT 3;

WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5)
SELECT x FROM n;

Free sign-in required · Google login available

SQL CTE (WITH) Practice Exercises

Check what you learned in “SQL CTE (WITH)”.

/ 3 solved

Fill in the blank

Start a common table expression.

 Totals AS (SELECT CustomerID, COUNT(*) AS N FROM Orders GROUP BY CustomerID)

✓ Correct! Not quite. Try again.

Multiple choice

Which keyword lets a CTE refer to itself?

RECURSIVE SELF REPEAT LOOP

✓ Correct! Not quite. Try again.

Code challenge

Using WITH, define OrderCounts (CustomerID, N) and select the CustomerID with the most orders.

Write the code in the live editor. Your answer is checked automatically.

Free sign-in required · Google login available

to save your exercise progress.

Finished this lesson?

Mark it complete to track your progress.

Stuck on something?

Ask the community in the CodesInTech forum.

Need a developer?

Learning on your own is great — and when you need professional help, our team builds custom software, websites and mobile apps for clients worldwide.

Explore Our Services

SQL CTE (WITH)

💡 Key Takeaways: SQL CTE (WITH)

SQL CTE (WITH) Code Example

SQL CTE (WITH) Practice Exercises

Key Takeaways: SQL CTE (WITH)