Subqueries, CTEs and window functions

Queries inside queries, named steps with WITH, and ranking with window functions.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗

main.sql

WITH course_avg AS (
    SELECT course_id, avg(grade) AS avg_grade
    FROM enrollments
    WHERE grade IS NOT NULL
    GROUP BY course_id
)
SELECT c.title, ca.avg_grade,
       rank() OVER (ORDER BY ca.avg_grade DESC) AS position
FROM course_avg ca
JOIN courses c ON c.id = ca.course_id
ORDER BY position, c.title;

setup/schema.sql

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

title	avg_grade	position
Algorithms	8.5	1
Databases	8.5	1

Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments. Equal averages share a rank.

main.sql

SELECT name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

setup/schema.sql

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

name
Linus

Students with no enrollment, with a correlated subquery.

rank() leaves gaps after ties (1, 1, 3), dense_rank() does not (1, 1, 2), row_number() numbers every row.
PARTITION BY restarts the numbering per group: row_number() OVER (PARTITION BY course_id ORDER BY grade DESC) finds the top student of each course.
Prefer NOT EXISTS to NOT IN (subquery): if the subquery returns a NULL, NOT IN matches nothing.

Subqueries, CTEs and window functions

Related topics

Official references