Common Table Expressions (CTE)
CTEs (WITH clause) are named temporary result sets defined at the start of a query. They replace nested subqueries with named, readable building blocks. A CTE can be referenced multiple times in the main query - and you can chain multiple CTEs.
CTE Benefits and Syntax
- Defined with WITH name AS (SELECT ...) before the main query
- Name is used like a table in the main query
- Multiple CTEs: WITH a AS (...), b AS (...) SELECT ... FROM a JOIN b ...
- Cleaner than nested subqueries - each step has a clear name
- Can be referenced multiple times (unlike subqueries in some dbs)
- CTEs are NOT always faster - they're primarily a readability tool
- Recursive CTEs use WITH RECURSIVE for hierarchical data
CTE Examples on ecommerce_db
-- Simple CTE: replace derived table with named step
WITH user_totals AS (
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
)
SELECT u.name, ut.total_spent
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
ORDER BY ut.total_spent DESC;
-- Multiple CTEs: chain steps clearly
WITH
confirmed_payments AS (
SELECT order_id, amount FROM payments WHERE paid_at IS NOT NULL
),
order_revenue AS (
SELECT o.user_id, SUM(p.amount) AS revenue
FROM orders o
JOIN confirmed_payments p ON o.id = p.order_id
GROUP BY o.user_id
)
SELECT u.name, COALESCE(r.revenue, 0) AS confirmed_revenue
FROM users u
LEFT JOIN order_revenue r ON u.id = r.user_id
ORDER BY confirmed_revenue DESC;
-- CTE + window function (clean combination)
WITH ranked_orders AS (
SELECT *,
RANK() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS rk
FROM orders
)
SELECT user_id, id AS top_order_id, total_amount
FROM ranked_orders
WHERE rk = 1; -- top order per userQuick Quiz
Tip
Tip
Practice Common Table Expressions CTE in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
CTEs = readable sub-queries, reusable, recursive hierarchies
Common Mistake
Warning
A common mistake with Common Table Expressions CTE is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Common Table Expressions CTE from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- CTEs (WITH clause) are named temporary result sets defined at the start of a query.
- Defined with WITH name AS (SELECT ...) before the main query
- Name is used like a table in the main query
- Multiple CTEs: WITH a AS (...), b AS (...) SELECT ... FROM a JOIN b ...