SQL interview question: Subquery vs CTE
Courses Above the Average Enrollment
The registrar wants to know which courses draw more students than a typical course, counting courses with no enrollments as 0.
Return course_name, department and enrollment_count for every course whose enrollment count is above the average enrollment count per course. Order by enrollment_count, highest first.
Skills: CTEs vs subqueries, LEFT JOIN with COUNT and comparing to an aggregate of aggregates
Tables
courses 15 rows
- course_id integer PK
- course_name text
- credits integer
- department text
| course_id | course_name | credits | department |
|---|---|---|---|
| 1 | SQL Basics | 3 | Computer Science |
| 2 | Python 101 | 3 | Computer Science |
| 3 | Data Structures | 4 | Computer Science |
enrollments 47 rows
- enrollment_id integer PK
- student_id integer → students
- course_id integer → courses
- semester text
- grade text
| enrollment_id | student_id | course_id | semester | grade |
|---|---|---|---|---|
| 1 | 1 | 1 | Fall 2024 | A |
| 2 | 1 | 3 | Fall 2024 | A |
| 3 | 1 | 5 | Fall 2024 | A- |
How to solve it
A subquery is a query nested inside another query, while a CTE (WITH name AS (...)) names that query once at the top so the main query can use it, even several times. Both usually return the same result. Choose a CTE when a step is reused, the query has several stages, or you need recursion, and a subquery for a short one-off filter.
Two steps, one problem
The question needs two steps. First count enrollments per course, then compare each count with the average of those counts. You can't do it in one GROUP BY, and the obvious shortcut fails:
SELECT c.course_name, COUNT(*) AS enrollment_count
FROM courses c
JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id
HAVING COUNT(*) > (SELECT AVG(COUNT(*)) FROM enrollments);SQLite stops with misuse of aggregate function COUNT(). You can't nest one aggregate inside another, because AVG(COUNT(*)) asks for an average of something that only exists after grouping. The per course counts have to become a result of their own first. A subquery and a CTE are two ways to name that result.
The subquery version
Put the grouped query in FROM as a derived table, then compute the average from a second copy of it:
SELECT course_name, department, enrollment_count
FROM (
SELECT c.course_name, c.department, COUNT(e.enrollment_id) AS enrollment_count
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id
) AS course_counts
WHERE enrollment_count > (
SELECT AVG(enrollment_count)
FROM (
SELECT COUNT(e.enrollment_id) AS enrollment_count
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id
) AS t
)
ORDER BY enrollment_count DESC;It returns the right three rows: SQL Basics with 10 enrollments, Python 101 with 8 and Calculus I with 6. The catch is that the counting logic appears twice. If you later decide to count only graded enrollments, you have to change both copies, and a missed edit gives a wrong average with no error.
The CTE version
A CTE names the step once at the top with WITH, and the rest of the query can use that name as often as it likes:
WITH course_counts AS (
SELECT c.course_name, c.department, COUNT(e.enrollment_id) AS enrollment_count
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id
)
SELECT course_name, department, enrollment_count
FROM course_counts
WHERE enrollment_count > (SELECT AVG(enrollment_count) FROM course_counts)
ORDER BY enrollment_count DESC;Same three rows, and the counting lives in one place. The query also reads top to bottom in the order you'd explain it: build the counts, then filter them. That's the main reason people reach for a CTE. For a single, short filter like WHERE gpa > (SELECT AVG(gpa) FROM students), a subquery is just as clear and there's nothing to reuse.
The average depends on the zero row
Art History has no enrollments. The LEFT JOIN keeps it with a count of 0, so the average is 47 enrollments over 15 courses, about 3.13. With an INNER JOIN, Art History disappears and the average becomes 47 over 14, about 3.36. Here both versions return the same three courses, because the next count down is 3. On other data the choice can change the answer, so say which one you mean.
Correlated subqueries
Some subqueries depend on the outer row. This one finds students whose GPA is above the average for their own grade level:
SELECT s.name, s.gpa, s.grade_level
FROM students s
WHERE s.gpa > (
SELECT AVG(s2.gpa)
FROM students s2
WHERE s2.grade_level = s.grade_level
)
ORDER BY s.grade_level, s.gpa DESC;The inner query reads s.grade_level from the outer row, so it can't be lifted into a CTE as it stands. A CTE can't see the row being filtered. To rewrite it, compute every group's average once and join to it:
WITH level_avg AS (
SELECT grade_level, AVG(gpa) AS avg_gpa
FROM students
GROUP BY grade_level
)
SELECT s.name, s.gpa, s.grade_level
FROM students s
JOIN level_avg la ON la.grade_level = s.grade_level
WHERE s.gpa > la.avg_gpa
ORDER BY s.grade_level, s.gpa DESC;Both return the same 14 students, from Tina Lopez in grade 9 to Uma Lee in grade 12. The CTE version makes the per level averages (2.867, 3.229, 3.433 and 3.75 when rounded) something you can select and check on their own.
What only a CTE can do
A CTE can refer to itself. WITH RECURSIVE builds rows step by step, which subqueries can't do:
WITH RECURSIVE n(x) AS (
SELECT 1
UNION ALL
SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;This returns the numbers 1 to 5. The same pattern walks a manager chain, as in the recursive org chart question.
Performance
Don't promise that one is faster. Most optimizers treat a CTE used once like the equivalent subquery. When a CTE is used twice, some engines compute it once and reuse the result, and others run it twice. SQLite 3.35 and later and PostgreSQL 12 and later let you ask for either with AS MATERIALIZED or AS NOT MATERIALIZED. If speed matters, compare the query plans on the real engine.
What to say in the interview
Write the CTE when a step is reused or the query has more than one stage, and say why: one definition, easier to test, reads in order. Mention that a subquery is fine for a one-off scalar filter, that correlated subqueries need a join to become CTEs, and that recursion needs a CTE. For more practice, try the CTE questions and the subquery questions.
Common questions
- Is a CTE faster than a subquery?
- Not by itself. Most optimizers handle a CTE used once like the same subquery. A CTE used several times may be computed once or several times depending on the engine, so check the query plan if it matters.
- Can every subquery be rewritten as a CTE?
- A subquery in FROM or an uncorrelated scalar subquery can move into a CTE directly. A correlated subquery reads the outer row, so you rewrite it as a CTE that computes every group's value, then join to it.
- Can a query have more than one CTE?
- Yes. Separate them with commas after a single
WITH, as inWITH a AS (...), b AS (...) SELECT .... A later CTE can read from an earlier one. - When should I use a temporary table instead of a CTE?
- A CTE lives only for the one statement. Use a temporary table when several separate queries need the same intermediate result, or when you want to index it.
- Why does AVG(COUNT(*)) fail?
- SQL doesn't allow one aggregate directly inside another. Compute the counts in a subquery or CTE first, then take the AVG over that result.
Practice on your iPhone
Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.
Other SQL dialects
- MySQL CTEs need MySQL 8.0 or later. On 5.7, use the derived table version with the counting subquery written twice.
- SQL Server AVG of an integer column returns an integer, so cast first with
AVG(CAST(enrollment_count AS DECIMAL(10,2)))to compare with the true average.