SQL Window Functions - Analyze Rows Without Grouping Them: – RANK, ROW_NUMBER, LEAD, LAG

Introduction

Imagine you’re analyzing student marks and need to assign ranks, compare with previous results, or compute running totals — all without collapsing your dataset using GROUP BY. That’s where Window Functions shine. They give you analytical power across rows while keeping each row intact.

What Are Window Functions?

Window functions perform calculations across a “window” of rows related to the current row. Unlike GROUP BY, they don’t collapse results. Instead, they add new analytical columns — such as ranks, moving averages, or differences — while preserving all rows.

Syntax

function_name(...) OVER (
  [PARTITION BY ...]
  [ORDER BY ...]
)

Sample Data – marks Table

CREATE TABLE marks (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO marks VALUES
(1, 'Aarav', 'Maths', 95),
(2, 'Diya', 'Maths', 85),
(3, 'Sneha', 'Maths', 92),
(4, 'Karan', 'Maths', 78),
(1, 'Aarav', 'Science', 90),
(2, 'Diya', 'Science', 80),
(3, 'Sneha', 'Science', 92),
(4, 'Karan', 'Science', 88);

1. RANK() – Ranking Students by Marks

SELECT name, subject, marks,
  RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS rank
FROM marks;

name   | subject | marks | rank
-------+---------+-------+------
Aarav  | Maths   | 95    | 1
Sneha  | Maths   | 92    | 2
Diya   | Maths   | 85    | 3
Karan  | Maths   | 78    | 4
Sneha  | Science | 92    | 1
Aarav  | Science | 90    | 2
Karan  | Science | 88    | 3
Diya   | Science | 80    | 4

2. ROW_NUMBER() – Assign Unique Order

SELECT name, subject, marks,
  ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC) AS row_num
FROM marks;

Unlike RANK(), this never gives duplicate numbers.

3. DENSE_RANK() – Avoid Gaps in Rank

SELECT name, subject, marks,
  DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS dense_rank
FROM marks;

Perfect for scoreboards where tied ranks shouldn't leave gaps.

4. LEAD() and LAG() – Compare Current vs Next/Previous

SELECT name, subject, marks,
  LEAD(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS next_marks,
  LAG(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS prev_marks
FROM marks;

name   | subject | marks | next_marks | prev_marks
--------+---------+-------+------------+-------------
Aarav  | Maths   | 95    | 92         | NULL
Sneha  | Maths   | 92    | 85         | 95
Diya   | Maths   | 85    | 78         | 92
Karan  | Maths   | 78    | NULL       | 85

Helps in comparisons like "How did this student score compared to others before/after?"

5. SUM() OVER – Cumulative Marks

SELECT name, subject, marks,
  SUM(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS cumulative_marks
FROM marks;

Real-World School Use Cases

• Ranking students within a class or subject
• Showing performance trend using LAG() and LEAD()
• Cumulative scores per student across subjects
• Generate roll call with ROW_NUMBER()

Best Practices

• Always define an ORDER BY clause in the window
• Use PARTITION BY to reset calculations per group
• Be cautious with NULLs — LAG and LEAD may return them
• Avoid mixing GROUP BY and window functions in the same SELECT unless necessary

Summary

Window functions are a powerful SQL feature that help you analyze data across rows without removing detail. Whether it’s building academic leaderboards, tracking score trends, or calculating cumulative performance, they add analytical depth with surgical precision — especially in school reporting systems where every row tells a story.

What’s Next?

Next: Pivoting and Unpivoting Data — learn how to transform rows into columns and vice versa for better reporting.