SQL Window Functions: OVER, PARTITION BY, ROW_NUMBER & RANK

Introduced in modern ANSI standards, Window Functions (also known as Analytic Functions) represent one of the most significant advancements in SQL. Unlike standard GROUP BY aggregations which collapse rows, window functions compute metrics across a sliding subset of rows (a window) while retaining every individual record in the output.

Figure 4.2: Window Function architecture showing row preservation with sliding partition calculations.

Anatomy of the OVER() Clause

function_name() OVER (
    [PARTITION BY partition_column]
    [ORDER BY sort_column]
    [ROWS|RANGE frame_clause]
)

Ranking Functions: ROW_NUMBER vs RANK vs DENSE_RANK

Consider three students where two tie with identical GPAs of 3.85:

SELECT 
    first_name,
    gpa,
    ROW_NUMBER() OVER (ORDER BY gpa DESC) AS row_num,
    RANK()       OVER (ORDER BY gpa DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY gpa DESC) AS dense_rnk
FROM students;

Partitioning: Ranking within Specific Groups

By adding PARTITION BY course, ranks reset back to 1 for every distinct course:

-- Rank students within their respective courses
SELECT 
    student_id,
    first_name,
    course,
    gpa,
    DENSE_RANK() OVER (
        PARTITION BY course 
        ORDER BY gpa DESC
    ) AS rank_in_course
FROM students;

Next, let us learn time-series analytics using SQL LEAD, LAG & Value Window Functions!