Lesson 21 · Develop an analysis

Give intermediate results a name

Task 1 of 3 · pixar · 0 results checked · 0 explanations saved

Opening your saved work…

Read the idea

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 task

Using a CTE named director_counts, return director and film_count for directors with at least two movies, ordered by director.

Predict or plan before trying

Give me a hint

Create the grouped result first, then filter its count in the outer SELECT.

Compare with a worked answer

WITH director_counts AS (SELECT director, COUNT(*) AS film_count FROM movies GROUP BY director) SELECT director, film_count FROM director_counts WHERE film_count >= 2 ORDER BY director;

Using an answer is useful practice. Record what you learned; it does not demonstrate independent mastery.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run your query to see the result here.

Explain your evidence

Would a single GROUP BY/HAVING query be equally correct? Which is clearer for this task?

My explanation or revision I would like help with this task

Your instructor can review account-saved work and help requests. Saving does not submit to Canvas.