Chapter 5: Window Functions

Window functions are one of the most advanced topics when learning SQL. Why Use Window Functions? In many analytical tasks, we want to perform calculations across rows that are somehow related to the current row, without collapsing the result into a grouped summary. That’s where window functions shine. Unlike aggregate functions like SUM() or AVG() that group rows together, window functions keep all individual rows intact while adding new computed columns based on a defined “window”.

What Is a Window?

A window is a set of rows related to the current row, defined using the OVER() clause. You can: Partition the data (e.g. per student, per department), Order it (e.g. by date or score), Apply functions like rankings, running totals, lag/lead comparisons

General Syntax:

FUNCTION(...) OVER (
  PARTITION BY column
  ORDER BY column)
    

RANKING FUNCTIONS: ROW_NUMBER(): Unique Row IDs Within a Partition

SELECT name, major_id,
       ROW_NUMBER() OVER (PARTITION BY major_id ORDER BY gpa DESC) AS rank
FROM students;
    

Ranks students within each major by GPA.

RANK() vs DENSE_RANK()

RANK() skips numbers when there's a tie.
DENSE_RANK() does not.

SELECT name, gpa,
       RANK() OVER (ORDER BY gpa DESC) AS gpa_rank,
       DENSE_RANK() OVER (ORDER BY gpa DESC) AS dense_gpa_rank
FROM students;
    

AGGREGATE WINDOW FUNCTIONS: RUNNING TOTAL (SUM)

SELECT student_id, grade,
       SUM(grade) OVER (PARTITION BY student_id ORDER BY exam_date) AS cumulative_score
FROM grades;
    

Calculates a running total for each student.

AVERAGE, MIN, MAX:

All typical aggregation functions can be used as window functions

SELECT student_id, grade,
       AVG(grade) OVER (PARTITION BY student_id) AS avg_per_student
FROM grades;
    

VALUE COMPARISON: LEAD, LAG

LEAD() and LAG() give access to adjacent rows

SELECT student_id, exam_date, grade,
       LAG(grade) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_grade,
       LEAD(grade) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_grade
FROM grades;
    

Useful for comparing progression or decline in performance.

Now that we unlocked a powerful feature set for advanced analytics within SQL. In the next chapter, we’ll focus on query performance and how to write fast, efficient SQL with indexes and query plans.