INTERACTIVE LECTURES
Develop and defend an analysis
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Give intermediate results a name
Step 1 of 3 · Database: Pixar & Box Office
READ & UNDERSTAND
A common table expression, introduced by WITH, names an intermediate query for the statement that follows. It can make a multi-stage analysis easier to read without creating a persistent table.
Name the grain of each CTE. A director-level summary joined back to movies repeats that summary for each movie; summing those repeated totals would inflate the result.
Refactoring should preserve the intended result. Compare values, duplicates, NULL handling, and ordering when the question requires an order.
Worked example
WITH counts AS (SELECT director, COUNT(*) AS films FROM movies GROUP BY director) SELECT director, films FROM counts ORDER BY director;
The counts CTE first creates one row per director. The outer SELECT chooses its fields and display order; the intermediate result exists only for this statement.
The practice applies a threshold to this director-level result. A direct HAVING clause can express the same logic, making it a useful equivalence check.
Your question
Using a CTE named director_counts, return director and film_count for directors with at least two movies, ordered by director.
Explain your evidence
Would a single GROUP BY/HAVING query be equally correct? Which is clearer for this task?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.