Review and Practice

Window Practice

Rank Within Groups

Window functions let a query compare rows inside a group without collapsing the grouped rows.

Program

Play the script to choose how many ranked rows per topic should appear.

window_rank_practice.sql 
CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);
INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);
WITH params(max_rank) AS (VALUES ()), ranked AS (SELECT student, topic, score, ROW_NUMBER() OVER (PARTITION BY topic ORDER BY score DESC, student) AS topic_rank FROM attempts) SELECT topic, student, score, topic_rank FROM ranked WHERE topic_rank <= (SELECT max_rank FROM params) ORDER BY topic, topic_rank;

CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);
INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);
WITH params(max_rank) AS (VALUES ()), ranked AS (SELECT student, topic, score, ROW_NUMBER() OVER (PARTITION BY topic ORDER BY score DESC, student) AS topic_rank FROM attempts) SELECT topic, student, score, topic_rank FROM ranked WHERE topic_rank <= (SELECT max_rank FROM params) ORDER BY topic, topic_rank;
partition PARTITION BY restarts the row number for each topic.
window order The window ORDER BY decides which row gets rank 1 inside each topic.
top rows Filtering by the generated rank keeps the top rows per group.