Loading Depth Study
Preparing your workspace
Subqueries and CTEs allow one query to use the result of another query as part of a larger operation.
A subquery is a query nested inside another query. It can be used in SELECT, WHERE, FROM, or other contexts depending on the problem. Common Table Expressions, created with WITH, provide a named temporary result that can make complex queries easier to read. CTEs are particularly useful when a report requires multiple logical steps. The goal is not to make every query complex, but to structure advanced queries clearly.
An analytics dashboard can first calculate average scores by course and then use that result to find courses performing above the overall average.
WITH course_scores AS (
SELECT
course_id,
AVG(score) AS average_score
FROM enrollments
GROUP BY course_id
)
SELECT course_id, average_score
FROM course_scores
WHERE average_score > 75
ORDER BY average_score DESC;The query creates an intermediate course_scores result and returns courses with an average score above 75.