Organize logic with subqueries and CTEs
Break complex questions into readable steps.
- Use subqueries and CTEs to compose multi-step SQL analysis.
A subquery is a query nested inside another query. A CTE (Common Table Expression) names an intermediate result with WITH, making larger statements easier to read and test. Both are useful when a question naturally has stages.
1WITH customer_totals AS (
2 SELECT customer_id, SUM(total) AS total_spent
3 FROM orders
4 GROUP BY customer_id
5)
6SELECT customer_id, total_spent
7FROM customer_totals
8WHERE total_spent >= 100
9ORDER BY total_spent DESC;CTEs help separate calculation steps from final filtering and sorting. In many engines, they are optimization boundaries only in specific cases; focus first on clarity and correctness.
Key takeaways
Use subqueries and CTEs to compose multi-step SQL analysis.
Use table and column names that make the query easy to read.
Check which rows a query affects before relying on its result.
Lesson quiz
5 questions · pass with 4 correct · up to 50 XP
Passing this quiz completes the lesson and keeps your streak going. Questions you miss come back in review sessions later.
Practice: write SQL queries
Write queries against small sample databases and run them locally in your browser. Each test starts from a fresh SQLite database.
Find top customers using a CTE
Return customer_id and total_spent for customers with total spending of at least 100, sorted from highest spending to lowest.
- Customers at or above threshold
Sample tables and rows
Each test rebuilds an in-memory database for the selected dialect before running your query.
CREATE TABLE orders (customer_id INTEGER, total REAL); INSERT INTO orders VALUES (1, 45), (1, 60), (2, 30), (2, 20), (3, 110);Using SQLite 3 (sql.js), an embedded database. Queries run locally in a worker against a fresh in-memory database for each test.
Questions about this lesson
Stuck? Ask. Figured something out? Share it. Explaining is one of the best ways to learn.
Loading posts…