Window functions compute across related rows while keeping one result row per input row. GROUP BY collapses a cohort to a single row; SUM(score) OVER (...) keeps the individual readings and attaches a cohort-related number to each one. The OVER clause defines that relationship.
Separate three choices: PARTITION BY chooses independent groups, the window ORDER BY chooses processing order within a group, and the frame chooses which of those rows an aggregate reads. A final ORDER BY controls how the whole query is presented.
Follow the data
ROW_NUMBER assigns different sequential numbers even to tied scores. RANK gives ties the same rank and leaves gaps afterward. DENSE_RANK gives ties the same rank without gaps. For scores 90, 90, 80, the ranks are respectively 1/2/3, 1/1/3 and 1/1/2. "Top two distinct scores, including ties" therefore calls for DENSE_RANK ordered by score alone.
The running sum needs a different order: score descending, then id ascending. An explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a prefix ending at this individual row. A RANGE frame based only on score includes all peers with the same score, which changes the running total at ties. Adding a unique id also makes row-by-row accumulation deterministic.
Compute the rank and running sum in a CTE, then filter standing <= 2 outside it. In SQLite window expressions cannot be used directly in WHERE. The outer filter sees the already computed values, including a running sum over the complete cohort. Do not filter the source prematurely and change the window's population.
LAG(value) reads a preceding row and LEAD a following row in the window order. The first or last row can have no neighbor, producing NULL unless a default is supplied. A difference from the previous event needs a stable time/id order for the same reason as a running total. The exact default frame matters for aggregate windows and FIRST_VALUE/LAST_VALUE; it does not turn ROW_NUMBER into a dense rank.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE readings(id INTEGER PRIMARY KEY, cohort TEXT NOT NULL, score INTEGER NOT NULL);
INSERT INTO readings VALUES (4,'a',90),(1,'a',90),(2,'a',80),(3,'a',70),(5,'b',50),(6,'b',40);
WITH ranked AS (
SELECT id, cohort, score,
DENSE_RANK() OVER (PARTITION BY cohort ORDER BY score DESC) AS standing,
SUM(score) OVER (PARTITION BY cohort ORDER BY score DESC, id ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_score
FROM readings
)
SELECT id, cohort, score, standing, running_score
FROM ranked WHERE standing <= 2
ORDER BY cohort, score DESC, id ASC;Diagnose the common failure
Adding id to the dense-rank order destroys score ties, because the full ordered key becomes unique. Leaving id out of a ROWS running-sum order leaves tied rows ambiguous. These two windows intentionally use different orders. Ranking and presentation are also separate: the final result still needs its own ORDER BY.
Try a change before building
Add a third reading with score 90 but a larger id. Predict all three rank variants and each prefix sum. Then change the frame end to UNBOUNDED FOLLOWING: identify why every row receives the full cohort total.
Move into practice
Partition without collapsing rows, choose tie semantics and define deterministic row frames. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.
The CS50 video reviews the relational foundations used here; the linked SQLite window-functions page covers the window-specific syntax and frame rules.