SQLite Basics: Break Queries into Steps with Common Table Expressions
An ordinary common table expression (CTE) gives a name to a query result for one statement. Use WITH to make filtering, aggregation, and small lookup sets easier to read. These examples are nonrecursive.
Prepare a scratch directory
Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.
start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation executes SQL in a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.
from pathlib import Path
import sqlite3
import sys
connection = sqlite3.connect(":memory:", isolation_level=None)
try:
statement = ""
for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
statement += line
if sqlite3.complete_statement(statement):
cursor = connection.execute(statement)
for row in cursor:
print("|".join("NULL" if value is None else str(value) for value in row))
statement = ""
if statement.strip():
raise ValueError("Unfinished SQL statement")
finally:
connection.close()1. Name a filtered result
Save as paid-orders.sql:
CREATE TABLE orders (id INTEGER, status TEXT, amount INTEGER);
INSERT INTO orders VALUES (1, 'paid', 30), (2, 'pending', 90), (3, 'paid', 50);
WITH paid AS (
SELECT id, amount FROM orders WHERE status = 'paid'
)
SELECT id, amount FROM paid WHERE amount >= 40 ORDER BY id;Run:
python3 run-sql.py paid-orders.sqlOutput:
3|50The outer query reads the named result. The name paid disappears when this statement ends; it is not a saved table or view.
2. Separate aggregation from comparison
Save as above-average.sql:
CREATE TABLE sales (team TEXT, amount INTEGER);
INSERT INTO sales VALUES ('East', 30), ('East', 50), ('West', 40), ('North', 60);
WITH totals AS (
SELECT team, SUM(amount) AS total FROM sales GROUP BY team
), benchmark AS (
SELECT AVG(total) AS average_total FROM totals
)
SELECT team, total FROM totals
WHERE total > (SELECT average_total FROM benchmark)
ORDER BY team;Run:
python3 run-sql.py above-average.sqlOutput:
East|80The first CTE computes team totals; the second computes their average. The final comparison uses one total per team, rather than an average over individual sales.
3. Include categories with no activity
Save as category-counts.sql:
CREATE TABLE tickets (id INTEGER, priority TEXT);
INSERT INTO tickets VALUES (1, 'high'), (2, 'high'), (3, 'low');
WITH priorities(priority, sort_order) AS (
VALUES ('high', 1), ('medium', 2), ('low', 3)
)
SELECT priorities.priority, COUNT(tickets.id) AS ticket_count
FROM priorities LEFT JOIN tickets ON tickets.priority = priorities.priority
GROUP BY priorities.priority, priorities.sort_order
ORDER BY priorities.sort_order;Run:
python3 run-sql.py category-counts.sqlOutput:
high|2
medium|0
low|1The small VALUES result supplies every category. A left join keeps medium even without tickets, and counting the non-NULL ticket ID yields zero. COUNT(*) would count its placeholder row instead. An outer ORDER BY makes display order explicit.
See SQLite’s WITH clause reference. CTEs improve structure but do not guarantee caching or faster execution; the query planner chooses how to execute them.
Clean up
In the same terminal, remove only these example files and the empty scratch directory:
rm -- run-sql.py paid-orders.sql above-average.sql category-counts.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dirAcknowledgements
This post was created with assistance from AI.