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.

max_rank
window_rank_practice.sql
Replay: real traced execution (multi-file project)
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 (1)), 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 (2)), 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;

  1. tables ← 1 row

    1CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);
    values this step1 rowtables

  2. attempts ← 6 rows

    1CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);3WITH params(max_rank) AS (VALUES (1)), 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;
    values this step6 rowsattempts

  3. result ← 2 rows

    2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);3WITH params(max_rank) AS (VALUES (1)), 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;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);
    values this step1 rowtables

  2. attempts ← 6 rows

    1CREATE TABLE attempts (student TEXT, topic TEXT, score INTEGER);2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);3WITH params(max_rank) AS (VALUES (2)), 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;
    values this step6 rowsattempts

  3. result ← 4 rows

    2INSERT INTO attempts VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 81), ('Mira', 'joins', 78), ('Ada', 'windows', 84), ('Lin', 'windows', 89), ('Mira', 'windows', 73);3WITH params(max_rank) AS (VALUES (2)), 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;
    values this step4 rowsresult
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.