Window functions

Chapter 7 10 code blocks 4289 words

If a student learns one advanced SQL topic, make it this one. Window functions are what separate someone who can query a database from someone who can analyse one — running totals, rankings, month-over-month change, "top 3 per category" — and they appear in almost every serious data analytics interview.

Availability: SQLite 3.25+ (2018), PostgreSQL, MySQL 8.0+, SQL Server 2012+. All modern versions support them.

The problem they solve

GROUP BY has one hard limitation: it destroys your rows. Once you group by course, the individual enrollments are gone. But most real questions need both at once:

• "Show every enrollment and how it compares to its course average."
• "Rank the students within each course."
• "Show each month's total and the running total so far."

A window function computes an aggregate over a set of rows related to the current row, while keeping the current row.

The OVER() clause

Any aggregate becomes a window function the moment you add OVER (...). Empty brackets mean "the window is every row in the result".

SELECT
    enrollment_id,
    grade,
    ROUND(AVG(grade) OVER (), 1)      AS overall_avg,
    grade - AVG(grade) OVER ()        AS diff,
    COUNT(*) OVER ()                  AS total_rows
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 4;

4 rows

1 ms

Fourteen rows kept, plus a summary column on each one. The general shape:

PARTITION BY

PARTITION BY is "GROUP BY that does not collapse". The function restarts for each partition.

SELECT
    e.enrollment_id,
    e.course_id,
    e.grade,
    ROUND(AVG(e.grade) OVER (PARTITION BY e.course_id), 1) AS course_avg,
    e.grade - AVG(e.grade) OVER (PARTITION BY e.course_id) AS vs_course
FROM enrollments e
WHERE e.grade IS NOT NULL AND e.course_id IN (1, 2)
ORDER BY e.course_id, e.grade DESC;

… first 15 of 278 rows

278 rows

1 ms

Every enrollment is still visible, and each carries its own course's average. Doing this with GROUP BY would require computing the averages separately and joining them back — two queries instead of one clause.

ROW_NUMBER, RANK, DENSE_RANK

Three ranking functions that differ only in how they handle ties. Our enrollment counts per course contain a genuine tie (courses 1 and 2 both have 5), which makes the difference visible:

WITH per_course AS (
    SELECT course_id, COUNT(*) AS n FROM enrollments GROUP BY course_id
)
SELECT
    course_id, n,
    ROW_NUMBER() OVER (ORDER BY n DESC) AS row_number,
    RANK()       OVER (ORDER BY n DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY n DESC) AS dense_rank
FROM per_course;

14 rows

1 ms

Top-N per group

"The best student in each course", "the 3 highest-value orders per customer" — this is the canonical window-function interview question, and there is no clean way to do it without one.

The pattern is always two steps: number the rows inside each partition, then keep the numbers you want.

WITH ranked AS (
    SELECT
        e.course_id,
        s.first_name,
        e.grade,
        ROW_NUMBER() OVER (
            PARTITION BY e.course_id
            ORDER BY e.grade DESC
        ) AS rn
    FROM enrollments e
    JOIN students s ON s.student_id = e.student_id
    WHERE e.grade IS NOT NULL
)
SELECT c.title, r.first_name, r.grade
FROM ranked r
JOIN courses c ON c.course_id = r.course_id
WHERE r.rn = 1                       -- change to rn <= 3 for a top-3
ORDER BY r.grade DESC;

14 rows

2 ms

LAG and LEAD

LAG(col, n) reaches back n rows; LEAD(col, n) reaches forward. Both default to 1. This is how you compute change over time — and it replaces the awkward self-join people used before window functions existed.

WITH monthly AS (
    SELECT strftime('%Y-%m', enrolled_at) AS month, COUNT(*) AS n
    FROM enrollments GROUP BY month
)
SELECT
    month,
    n,
    LAG(n)  OVER (ORDER BY month)                    AS prev_month,
    n - LAG(n) OVER (ORDER BY month)                 AS change,
    ROUND(100.0 * (n - LAG(n) OVER (ORDER BY month))
          / LAG(n) OVER (ORDER BY month), 1)         AS pct_change
FROM monthly
ORDER BY month;

4 rows

1 ms

The first row is NULL because there is no previous month — correct behaviour, not an error. Supply a default if a number is required: LAG(n, 1, 0).

Running totals and frames

Adding ORDER BY inside OVER() changes the meaning fundamentally: the window becomes "all rows from the start up to the current row" instead of "all rows". That is a running total.

WITH monthly AS (
    SELECT strftime('%Y-%m', enrolled_at) AS month, COUNT(*) AS n
    FROM enrollments GROUP BY month
)
SELECT
    month, n,
    SUM(n) OVER (ORDER BY month)                     AS running_total,
    SUM(n) OVER ()                                   AS grand_total,
    ROUND(100.0 * SUM(n) OVER (ORDER BY month)
                / SUM(n) OVER (), 1)                 AS pct_of_total
FROM monthly ORDER BY month;

4 rows

1 ms

Note the same function, SUM(n), gives a running total with ORDER BY and a grand total without it. Both in one query.

Frames

The frame spells out which rows the window covers. When you write ORDER BY and no frame, the default is:

Moving averages

A moving (rolling) average smooths a noisy series. Here it is over the grades of one course, in enrollment order:

SELECT
    e.enrolled_at,
    e.grade,
    ROUND(AVG(e.grade) OVER (
        ORDER BY e.enrolled_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3
FROM enrollments e
WHERE e.course_id = 2 AND e.grade IS NOT NULL
ORDER BY e.enrolled_at;

… first 15 of 146 rows

146 rows

1 ms

The first two rows average over fewer than three values, because there are not three rows yet — the window shrinks at the edges rather than returning NULL. If a report must only show full windows, filter on COUNT(*) OVER (same frame) = 3.

NTILE and PERCENT_RANK

NTILE(n) splits ordered rows into n roughly equal buckets — quartiles, deciles, customer segments.

SELECT
    enrollment_id, grade,
    NTILE(4)      OVER (ORDER BY grade DESC) AS quartile,
    ROUND(PERCENT_RANK() OVER (ORDER BY grade DESC), 2) AS pct_rank
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC;

… first 15 of 1826 rows

1826 rows

3 ms

Window vs GROUP BY

They combine happily — a window function can operate on the output of a GROUP BY in the same query:

SELECT
    course_id,
    COUNT(*)                                   AS enrollments,
    SUM(COUNT(*)) OVER ()                      AS total_enrollments,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all,
    RANK() OVER (ORDER BY COUNT(*) DESC)       AS popularity_rank
FROM enrollments
GROUP BY course_id
ORDER BY enrollments DESC;

14 rows

1 ms

SUM(COUNT(*)) OVER () looks strange but follows directly from the execution order: COUNT(*) is computed at the grouping step, and the window function then runs over those grouped rows. "Each row's share of the total" is one of the most-requested report columns there is, and this is the one-query way to produce it.

Practice set

-- 1. Every enrollment with its course average and the difference
SELECT enrollment_id, course_id, grade,
       ROUND(AVG(grade) OVER (PARTITION BY course_id), 1) AS course_avg
FROM enrollments WHERE grade IS NOT NULL ORDER BY course_id, grade DESC;

-- 2. Rank students by overall average grade
WITH avg_per_student AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE grade IS NOT NULL GROUP BY student_id
)
SELECT s.first_name, ROUND(a.avg_grade,1) AS avg_grade,
       RANK() OVER (ORDER BY a.avg_grade DESC) AS rank
FROM avg_per_student a JOIN students s USING (student_id);

-- 3. Best grade in each course (top-1 per group)
WITH r AS (
    SELECT course_id, student_id, grade,
           ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rn
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT * FROM r WHERE rn = 1;

-- 4. Each student's enrollments numbered in date order
SELECT student_id, enrolled_at, course_id,
       ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enrolled_at) AS nth_course
FROM enrollments ORDER BY student_id, enrolled_at;

-- 5. Days between a student's consecutive enrollments
SELECT student_id, enrolled_at,
       LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at) AS prev,
       JULIANDAY(enrolled_at)
         - JULIANDAY(LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at)) AS days_gap
FROM enrollments ORDER BY student_id, enrolled_at;

-- 6. Running total of enrollments over time
SELECT enrolled_at, COUNT(*) AS n,
       SUM(COUNT(*)) OVER (ORDER BY enrolled_at) AS running_total
FROM enrollments GROUP BY enrolled_at ORDER BY enrolled_at;

-- 7. Split grades into quartiles
SELECT enrollment_id, grade, NTILE(4) OVER (ORDER BY grade DESC) AS quartile
FROM enrollments WHERE grade IS NOT NULL;

-- 8. Each course's share of total revenue
SELECT c.title, SUM(c.price) AS revenue,
       ROUND(100.0 * SUM(c.price) / SUM(SUM(c.price)) OVER (), 1) AS pct
FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title ORDER BY revenue DESC;

Result 1

… first 15 of 1826 rows

1826 rows

Result 2

… first 15 of 300 rows

300 rows

Result 3

14 rows

Result 4

… first 15 of 2500 rows

2500 rows

Result 5

… first 15 of 2500 rows

2500 rows

Result 6

… first 15 of 94 rows

94 rows

Result 7

… first 15 of 1826 rows

1826 rows

Result 8

14 rows

15 ms

Interview questions

Q: What is a window function and how does it differ from an aggregate?

Both compute over a set of rows, but an aggregate with GROUP BY collapses those rows into one, whereas a window function returns a value for every row and keeps them all.

Q: ROW_NUMBER vs RANK vs DENSE_RANK?

On ties: ROW_NUMBER assigns different consecutive numbers; RANK assigns the same number and then skips (1,1,3); DENSE_RANK assigns the same number with no gap (1,1,2).

Q: How do you get the top 3 rows per group?

ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) in a CTE, then WHERE rn <= 3 in the outer query.

Q: Why can't you filter on a window function in WHERE?

Because window functions are evaluated with SELECT (step 5), after WHERE (step 2). The value does not exist yet. Wrap the query in a CTE or derived table and filter outside.

Q: What does ORDER BY inside OVER() change?

It orders rows within the partition and, for aggregates, switches the default frame to "from the start of the partition to the current row" — turning SUM into a running total.

Q: How do you compute month-over-month growth?

(n - LAG(n) OVER (ORDER BY month)) * 100.0 / LAG(n) OVER (ORDER BY month). Make sure every month exists as a row first, or LAG will step to the wrong period.

Q: ROWS vs RANGE in a frame?

ROWS counts physical rows; RANGE treats all rows with the same ORDER BY value as one unit. They differ only when there are duplicate ordering values.

Q: What is SUM(COUNT(*)) OVER () doing?

The inner COUNT(*) aggregates per group; the window function then sums those group totals across all groups — giving each group's share of the overall total in a single query.

Summary

• A window function computes over related rows without collapsing them — the detail stays, the summary is added as a column.
• OVER () = all rows · OVER (PARTITION BY x) = restart per group · OVER (ORDER BY x) = running / cumulative.
• Ranking: ROW_NUMBER (always unique), RANK (ties share, then skip), DENSE_RANK (ties share, no gaps). Add a tie-breaker to keep ROW_NUMBER deterministic.
• Top-N per group = ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) in a CTE, then filter. This is the classic interview task.
• You cannot filter a window function in WHERE — it is computed at the SELECT step. Use a CTE.
• LAG/LEAD reach to neighbouring rows — generate a complete date series first, or period-over-period maths silently compares the wrong periods.
• Frames (ROWS BETWEEN ...) define the window: running totals, moving averages, look-ahead sums.
• NTILE(n) buckets rows into segments (quartiles, RFM scores); PERCENT_RANK gives a 0–1 relative position.
• Window functions can sit on top of a GROUP BY — SUM(COUNT(*)) OVER () gives each group's share of the total.

Next: Chapter 8 — string and date functions plus CASE WHEN, the tools for reshaping and categorising values inside a query.