SQL query optimization
Goal: rewrite a slow query so it produces byte-identical output, faster, in the
target dialect, with the exact required formatting (usually: no comments, a
single statement, terminated with ;).
Understand the original. Read the query file, then run it under
timeagainst the database to get a baseline (sqlite3 db < query.sql > out.txt). Identify the cost source: correlated subqueries in the SELECT list, missing indexes, repeated full scans, or a window function over a large table. Capture the reference output from the ORIGINAL query run to completion on the real database — never reconstruct it by hand; if it is slow, run it once in the background with a generous timeout and diff against that saved output.Inspect the schema.
sqlite3 db ".schema"; list indexes withSELECT name, tbl_name FROM sqlite_master WHERE type='index';; confirm uniqueness assumptions viaCOUNT(*)vsCOUNT(DISTINCT …).Rewrite with fast primitives.
- Hoist correlated subqueries out of the SELECT list into
WITHCTEs that are computed once. - Compute per-group maxima/minima with plain aggregation (
MAX(c),MIN(x)) plus a join on that value, NOTROW_NUMBER() OVER (…)— window functions are far slower in SQLite. - Preserve the exact ORDER BY / LIMIT / tie-break semantics of the original, so ties resolve to the same rows.
- Hoist correlated subqueries out of the SELECT list into
The deliverable is the query alone — no indexes or schema changes survive grading. Verify the final query runs standalone on the clean database and is byte-identical, not merely close.
Verify byte-identical output. Run both the original and the rewrite and
difftheir outputs — they must be identical. Then time the rewrite and confirm it finishes well inside the budget (a grader may run it under a strict time limit).Write the final file exactly as specified — single statement, no comments, dialect-correct, semicolon-terminated — then re-read it and run it once to confirm it executes cleanly.