Advanced SQL with SQLite: Subqueries, CTEs, Window Functions
Beyond SELECT and JOIN
Once you can filter, join and group data, real questions quickly demand more. Which students scored above their department's average? Who came first in each course? What is the running total of payments? Which students have not registered for anything? Advanced SQL answers all of these in a single query, using subqueries, CASE expressions, common table expressions (CTEs) and window functions. Knowing how to keep queries fast and safe, with indexes, transactions and parameterised queries, is just as important.
This tutorial covers advanced SQL with SQLite, run from Python's built-in sqlite3 module so that you can try every example without installing anything: LEFT JOIN for missing data, HAVING, subqueries (including correlated subqueries), CASE, CTEs and recursive CTEs, window functions such as RANK and running totals, UPSERT, indexes with EXPLAIN QUERY PLAN, transactions, and protecting against SQL injection. It builds on the SQL basics tutorial.
The Example Database
import sqlite3
con = sqlite3.connect(":memory:") # a temporary database in memory
con.executescript("""
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, level INTEGER);
CREATE TABLE courses (code TEXT PRIMARY KEY, title TEXT, units INTEGER);
CREATE TABLE results (student_id INTEGER, course_code TEXT, score INTEGER,
PRIMARY KEY (student_id, course_code));
INSERT INTO students VALUES
(1, 'Ada', 'Physics', 100), (2, 'Bayo', 'Physics', 200), (3, 'Chidi', 'Maths', 100),
(4, 'Dami', 'Maths', 200), (5, 'Efe', 'Physics', 100), (6, 'Femi', 'Computer', 100);
INSERT INTO courses VALUES
('MTH101', 'Elementary Maths', 3), ('PHY101', 'General Physics', 3), ('CSC101', 'Intro to Computing', 2);
INSERT INTO results VALUES
(1, 'MTH101', 72), (1, 'PHY101', 81), (2, 'MTH101', 58), (2, 'PHY101', 64),
(3, 'MTH101', 90), (3, 'CSC101', 77), (4, 'MTH101', 45), (5, 'PHY101', 81), (5, 'CSC101', 38);
""")
def show(sql, params=()):
cur = con.execute(sql, params)
print(" | ".join(col[0] for col in cur.description))
for row in cur:
print(" | ".join(str(v) for v in row))
show("SELECT COUNT(*) AS students FROM students")
Output:
students 6
Every advanced SQL example below uses this database. The helper show prints the column names and then each row. Note that student 6, Femi, has no results at all.
LEFT JOIN: Including Rows with No Match
An ordinary (inner) JOIN keeps only rows that match in both tables, so Femi would disappear. A LEFT JOIN keeps every row from the left table, filling the missing columns with NULL.
show("""
SELECT s.name, COUNT(r.course_code) AS courses_taken
FROM students s
LEFT JOIN results r ON r.student_id = s.id
GROUP BY s.id
ORDER BY courses_taken, s.name
""")
Output:
name | courses_taken Femi | 0 Dami | 1 Ada | 2 Bayo | 2 Chidi | 2 Efe | 2
Use COUNT(r.course_code), not COUNT(*). COUNT(column) ignores NULLs, so Femi correctly gets 0 instead of 1. To find only the students with no results, add WHERE r.student_id IS NULL.
HAVING: Filtering Groups
WHERE filters individual rows before grouping. HAVING filters whole groups after grouping, so it can use aggregates such as AVG.
show("""
SELECT course_code, COUNT(*) AS n, ROUND(AVG(score), 1) AS avg_score
FROM results
GROUP BY course_code
HAVING AVG(score) >= 65
""")
Output:
course_code | n | avg_score MTH101 | 4 | 66.3 PHY101 | 3 | 75.3
CSC101 averages (77 + 38) ÷ 2 = 57.5, so it is filtered out.
Subqueries
A subquery is a query inside another query, in brackets.
A scalar subquery (one value)
show("""
SELECT student_id, course_code, score
FROM results
WHERE score > (SELECT AVG(score) FROM results)
ORDER BY score DESC
""")
Output:
student_id | course_code | score 3 | MTH101 | 90 1 | PHY101 | 81 5 | PHY101 | 81 3 | CSC101 | 77 1 | MTH101 | 72
The overall average is 606 ÷ 9 ≈ 67.3.
IN with a subquery
show("""
SELECT name FROM students
WHERE id IN (SELECT student_id FROM results WHERE course_code = 'CSC101')
""")
Output:
name Chidi Efe
A correlated subquery
A correlated subquery refers to the outer query, so it is re-evaluated for each outer row. Here, each student's score is compared with the average for that course:
show("""
SELECT s.name, r.course_code, r.score
FROM results r JOIN students s ON s.id = r.student_id
WHERE r.score > (SELECT AVG(r2.score) FROM results r2
WHERE r2.course_code = r.course_code)
ORDER BY r.course_code, r.score DESC
""")
Output:
name | course_code | score Chidi | CSC101 | 77 Chidi | MTH101 | 90 Ada | MTH101 | 72 Ada | PHY101 | 81 Efe | PHY101 | 81
EXISTS (subquery) is a related test that is true if the subquery returns any rows at all, and it is often used with correlated subqueries.
CASE: If–Else Inside a Query
show("""
SELECT s.name, r.course_code, r.score,
CASE WHEN r.score >= 70 THEN 'A'
WHEN r.score >= 60 THEN 'B'
WHEN r.score >= 50 THEN 'C'
WHEN r.score >= 45 THEN 'D'
ELSE 'F' END AS grade
FROM results r JOIN students s ON s.id = r.student_id
WHERE s.dept = 'Physics'
ORDER BY s.name, r.course_code
""")
Output:
name | course_code | score | grade Ada | MTH101 | 72 | A Ada | PHY101 | 81 | A Bayo | MTH101 | 58 | C Bayo | PHY101 | 64 | B Efe | CSC101 | 38 | F Efe | PHY101 | 81 | A
CASE also works inside aggregates. For example, SUM(CASE WHEN score >= 45 THEN 1 ELSE 0 END) counts the passes.
Common Table Expressions (WITH)
A CTE gives a name to a subquery, so a complicated query can be built in readable steps.
show("""
WITH graded AS (
SELECT r.student_id, c.units,
CASE WHEN score >= 70 THEN 5 WHEN score >= 60 THEN 4
WHEN score >= 50 THEN 3 WHEN score >= 45 THEN 2 ELSE 0 END AS point
FROM results r JOIN courses c ON c.code = r.course_code
)
SELECT s.name, ROUND(1.0 * SUM(point * units) / SUM(units), 2) AS gpa
FROM graded g JOIN students s ON s.id = g.student_id
GROUP BY s.id
ORDER BY gpa DESC, s.name
""")
Output:
name | gpa Ada | 5.0 Chidi | 5.0 Bayo | 3.5 Efe | 3.0 Dami | 2.0
The GPA is the sum of grade point × units, divided by the total units. Multiplying by 1.0 forces decimal division, because dividing one integer by another in SQLite discards the remainder.
Recursive CTEs
A recursive CTE refers to itself. It is used for sequences and for hierarchies such as organisation charts or course prerequisites.
show("""
WITH RECURSIVE powers(n, value) AS (
SELECT 0, 1
UNION ALL
SELECT n + 1, value * 2 FROM powers WHERE n < 5
)
SELECT n, value FROM powers
""")
Output:
n | value 0 | 1 1 | 2 2 | 4 3 | 8 4 | 16 5 | 32
Window Functions
Window functions are among the most useful advanced SQL features. A GROUP BY collapses each group into a single row. A window function calculates across a group of rows but keeps every row. The OVER (...) clause defines the window: PARTITION BY splits the rows into groups, and ORDER BY orders them within each group.
Ranking within each course
show("""
SELECT course_code, s.name, score,
RANK() OVER (PARTITION BY course_code ORDER BY score DESC) AS rnk,
ROW_NUMBER() OVER (PARTITION BY course_code ORDER BY score DESC, s.name) AS row_num
FROM results r JOIN students s ON s.id = r.student_id
WHERE course_code = 'PHY101'
""")
Output:
course_code | name | score | rnk | row_num PHY101 | Ada | 81 | 1 | 1 PHY101 | Efe | 81 | 1 | 2 PHY101 | Bayo | 64 | 3 | 3
RANK gives tied rows the same rank and then skips (1, 1, 3). DENSE_RANK does not skip (1, 1, 2). ROW_NUMBER always numbers 1, 2, 3, so a tiebreaker in its ORDER BY makes it repeatable.
Top student per course
show("""
WITH ranked AS (
SELECT course_code, s.name, score,
RANK() OVER (PARTITION BY course_code ORDER BY score DESC) AS rnk
FROM results r JOIN students s ON s.id = r.student_id
)
SELECT course_code, name, score FROM ranked WHERE rnk = 1 ORDER BY course_code, name
""")
Output:
course_code | name | score CSC101 | Chidi | 77 MTH101 | Chidi | 90 PHY101 | Ada | 81 PHY101 | Efe | 81
Window functions cannot be used directly in WHERE, which is why the ranking is computed in a CTE first.
Running totals and comparing with the group average
con.executescript("""
CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5000), (2, 2000), (3, 8000), (4, 1000);
""")
show("""
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS running_total,
amount - LAG(amount) OVER (ORDER BY day) AS change,
ROUND(AVG(amount) OVER (), 0) AS overall_avg
FROM payments
""")
Output:
day | amount | running_total | change | overall_avg 1 | 5000 | 5000 | None | 4000.0 2 | 2000 | 7000 | -3000 | 4000.0 3 | 8000 | 15000 | 6000 | 4000.0 4 | 1000 | 16000 | -7000 | 4000.0
LAG reads the previous row's value (and LEAD the next), and an empty OVER () makes the whole table one window. NULL appears as None in Python.
UPSERT: Insert or Update
Inserting a row whose primary key already exists normally fails. ON CONFLICT ... DO UPDATE updates the existing row instead:
con.execute("""
INSERT INTO results VALUES (4, 'MTH101', 62)
ON CONFLICT (student_id, course_code) DO UPDATE SET score = excluded.score
""")
show("SELECT * FROM results WHERE student_id = 4")
Output:
student_id | course_code | score 4 | MTH101 | 62
excluded refers to the row that was being inserted. Dami's resit score replaced the original 45.
Indexes and EXPLAIN QUERY PLAN
Without an index, a filter such as WHERE dept = 'Physics' must check every row: a full table scan, which is slow for millions of rows. An index is a sorted lookup structure, like the index at the back of a book, that lets the database jump straight to the matching rows.
def plan(sql):
for row in con.execute("EXPLAIN QUERY PLAN " + sql):
print(row[-1])
query = "SELECT name FROM students WHERE dept = 'Physics'"
plan(query)
con.execute("CREATE INDEX idx_students_dept ON students(dept)")
plan(query)
Output:
SCAN students SEARCH students USING INDEX idx_students_dept (dept=?)
Indexes speed up reading but slow down inserts and updates slightly, and they use extra space. Index the columns you frequently filter, join or sort on. Primary keys are indexed automatically.
Transactions
A transaction groups several statements so that either all of them take effect or none do. In Python, using the connection as a context manager commits on success and rolls back on an error:
con.executescript("""
CREATE TABLE wallets (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0));
INSERT INTO wallets VALUES ('Ada', 5000), ('Bayo', 1000);
""")
def transfer(src, dst, amount):
try:
with con: # one transaction
con.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, dst))
con.execute("UPDATE wallets SET balance = balance - ? WHERE owner = ?", (amount, src))
print("transfer ok")
except sqlite3.IntegrityError:
print("transfer failed and was rolled back")
transfer("Ada", "Bayo", 2000)
transfer("Bayo", "Ada", 9000) # would make Bayo's balance negative
show("SELECT * FROM wallets")
Output:
transfer ok transfer failed and was rolled back owner | balance Ada | 3000 Bayo | 3000
In the failed transfer, Ada had already been credited before Bayo's debit broke the CHECK rule. The rollback undid that credit, so no money was created out of nothing.
Parameterised Queries and SQL Injection
Never build SQL by pasting user input into a string. A malicious input can change the meaning of the query. This attack is called SQL injection.
user_input = "x' OR '1'='1"
unsafe = f"SELECT name FROM students WHERE name = '{user_input}'"
print(len(con.execute(unsafe).fetchall()), "rows leaked") # every student!
safe = con.execute("SELECT name FROM students WHERE name = ?", (user_input,)).fetchall()
print(len(safe), "rows")
Output:
6 rows leaked 0 rows
With the ? placeholder, the database treats the whole input as a plain value, never as SQL code. Always use placeholders for values that come from users, files or the network.
Common Mistakes with Advanced SQL
- Using WHERE with an aggregate, such as
WHERE AVG(score) > 60. Aggregates go in HAVING. - Using
COUNT(*)after a LEFT JOIN, which counts the NULL-filled row as 1. - Testing for NULL with
= NULL. UseIS NULL, because NULL is not equal to anything, not even NULL. - Filtering on a window function in WHERE. Compute it in a CTE or subquery first.
- Integer division: in SQLite,
7 / 2is 3. Multiply by 1.0 for a decimal result. - Building queries with f-strings from user input. Use placeholders.
Advanced SQL: Practice Exercises
- List the students who have no results, using a LEFT JOIN.
- For each department, show the number of students and the highest score achieved by any of its students.
- Use DENSE_RANK to rank all the results in MTH101.
- Use a recursive CTE to list the numbers 1 to 5 with their squares.
Sample solutions:
show("""
SELECT s.name FROM students s
LEFT JOIN results r ON r.student_id = s.id
WHERE r.student_id IS NULL
""")
show("""
SELECT s.dept, COUNT(DISTINCT s.id) AS students, MAX(r.score) AS best
FROM students s LEFT JOIN results r ON r.student_id = s.id
GROUP BY s.dept ORDER BY s.dept
""")
show("""
SELECT s.name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS place
FROM results r JOIN students s ON s.id = r.student_id
WHERE course_code = 'MTH101'
""")
show("""
WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 5)
SELECT n, n * n AS square FROM nums
""")
Output:
name Femi dept | students | best Computer | 1 | None Maths | 2 | 90 Physics | 3 | 81 name | score | place Chidi | 90 | 1 Ada | 72 | 2 Dami | 62 | 3 Bayo | 58 | 4 n | square 1 | 1 2 | 4 3 | 9 4 | 16 5 | 25
Dami's MTH101 score is now 62, after the UPSERT earlier.
Conclusion
Advanced SQL turns complex questions into single queries. LEFT JOIN keeps unmatched rows, so missing data can be found. HAVING filters groups. Subqueries, including correlated ones, compare rows with computed values, and CASE adds if–else logic. CTEs, written with WITH and optionally recursive, build queries in readable steps. Window functions such as RANK, ROW_NUMBER, LAG and running SUMs calculate across rows without collapsing them. UPSERT inserts or updates in one statement, indexes turn scans into fast searches, transactions make changes all-or-nothing, and parameterised queries block SQL injection.