Develop an analysis
Part 1 of 3
Give intermediate results a name
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.
Part 2 of 3
Separate a clear query from a fast query
An index can help locate rows but also consumes space and adds maintenance work when data changes. A faster claim requires measurement on the relevant workload.
EXPLAIN QUERY PLAN describes a strategy; it is not a timing result. Tiny classroom datasets are useful for learning plans, but do not establish production-scale performance.
A view saves a query definition for reuse, not a frozen result snapshot. Triggers run on configured changes and need explicit tests for their side effects. Practice creating views, indexes, and triggers in a disposable desktop database before applying them elsewhere.
Worked example
SELECT id, title FROM movies WHERE year >= 2000 ORDER BY id;
The filter includes the boundary year 2000. The final id ordering is part of this output contract and must be retained when comparing an equivalent rewrite.
Run EXPLAIN QUERY PLAN followed by this SELECT to inspect the chosen strategy. It does not measure elapsed time or prove that an index would improve a larger workload.
Part 3 of 3
Understand ranking without collapsing rows
GROUP BY collapses rows into group summaries. A window function can attach a group calculation or rank while retaining the individual rows. The choice depends on the desired output grain.
ROW_NUMBER assigns positions within an ordering; RANK gives tied values the same rank and leaves gaps. Choose a tie policy deliberately and add a deterministic tie-breaker when unique positions are required.
The current browser SQLite engine does not support window syntax. The runnable exercise below explores the ordering first. Try SELECT id, title, ROW_NUMBER() OVER (ORDER BY year DESC, id) AS position FROM movies in your assigned modern desktop SQLite environment.
Worked example
SELECT id, title, year FROM movies ORDER BY year DESC, id;
The preview places newer release years first and breaks ties by id. It establishes the ordering a ROW_NUMBER example would use in a modern engine.
LIMIT 5 returns exactly five rows when at least five exist. A rank-based rule may include more rows when several movies tie, depending on the chosen ranking function and filter.