24. Window Functions -- Advanced Row-Level Analytics

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• ROW_NUMBER -- unique sequential row number
• RANK, DENSE_RANK -- ranking with/without gaps
• OVER clause -- PARTITION BY and ORDER BY
• Running totals and moving averages
• Top N per group (without GROUP BY)
• LAG, LEAD -- access previous/next rows
• Row comparison and trends
• Common window function mistakes
• Best practices

GROUP BY aggregates into single row per group. Window functions compute per row while keeping row details. This article teaches advanced row-level analytics.

Why Window Functions Matter for Backend Developers

APIs need per-row analytics:

Frontend asks: "Show each exam result with:
                - Student's rank within their class
                - That student's previous exam marks (for trend)
                - Top 3 in class (without filtering)"
    v
Backend needs: Keep all rows, add ranking and comparison columns
    v
Window functions: Per-row analytics without GROUP BY
    v
Complete ranked list with trends

Window functions solve "analyze each row within groups" problems.

Window Function Syntax

SELECT 
    Column,
    ROW_NUMBER() OVER (PARTITION BY GroupColumn ORDER BY SortColumn) AS Rank
FROM Table;

OVER clause parts:

• PARTITION BY: Divide into groups (like GROUP BY)
• ORDER BY: Rank order within partition

---

Example 1: ROW_NUMBER (unique per group)

Rank students within their class by marks:

SELECT 
    s.Name AS Student,
    s.ClassName,
    r.MarksObtained,
    ROW_NUMBER() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS RankInClass
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
ORDER BY s.ClassName, RankInClass;

Result (ranking):

Student     | ClassName | MarksObtained | RankInClass
Ravi Kumar  | 10-A      | 92            | 1
Priya Sharma| 10-A      | 85            | 2
Sneha Patel | 10-A      | 78            | 3
Arjun Reddy | 10-B      | 88            | 1
Kiran Rao   | 10-B      | 82            | 2

ROW_NUMBER: Unique number per partition (class). 1, 2, 3 per class.

Example 2: RANK vs DENSE_RANK (ties)

Ranking with tied marks:

SELECT 
    s.Name AS Student,
    s.ClassName,
    r.MarksObtained,
    RANK() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS RankGaps,
    DENSE_RANK() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS RankNoDuplicates
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
ORDER BY s.ClassName, RankGaps;

Result (ranking with ties):

Student     | ClassName | MarksObtained | RankGaps | RankNoDuplicates
Ravi Kumar  | 10-A      | 92            | 1        | 1
Priya Sharma| 10-A      | 92            | 1        | 1        (tied)
Sneha Patel | 10-A      | 85            | 3        | 2        (RANK skips 2)
Arjun Reddy | 10-A      | 78            | 4        | 3

RANK: 1, 1, 3 (gaps on tie). DENSE_RANK: 1, 1, 2 (no gaps).

Use RANK for olympiad-style (skip ranks). DENSE_RANK for continuous ranking.

Example 3: Running Total (cumulative sum)

Track cumulative fee payments per student:

SELECT 
    s.Name AS Student,
    p.PaidOn,
    p.Amount,
    SUM(p.Amount) OVER (
        PARTITION BY p.FeeAccountId 
        ORDER BY p.PaidOn
    ) AS CumulativeAmount
FROM FeePayment p
INNER JOIN FeeAccount f ON p.FeeAccountId = f.FeeAccountId
INNER JOIN Student s ON f.StudentId = s.StudentId
ORDER BY s.Name, p.PaidOn;

Result (running total):

Student     | PaidOn     | Amount | CumulativeAmount
Ravi Kumar  | 2024-05-01 | 10000  | 10000
Ravi Kumar  | 2024-05-15 | 5000   | 15000
Ravi Kumar  | 2024-06-01 | 5000   | 20000
Priya Sharma| 2024-05-10 | 15000  | 15000

SUM() OVER with ORDER BY: Cumulative sum per student.

Example 4: LAG and LEAD (previous/next row)

Compare current exam to previous exam:

SELECT 
    s.Name AS Student,
    e.ExamName,
    r.MarksObtained,
    LAG(r.MarksObtained) OVER (
        PARTITION BY s.StudentId 
        ORDER BY e.ExamDate
    ) AS PreviousMarks,
    LEAD(r.MarksObtained) OVER (
        PARTITION BY s.StudentId 
        ORDER BY e.ExamDate
    ) AS NextMarks,
    r.MarksObtained - LAG(r.MarksObtained) OVER (
        PARTITION BY s.StudentId 
        ORDER BY e.ExamDate
    ) AS ChangeFromPrevious
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
ORDER BY s.Name, e.ExamDate;

Result (trend):

Student     | ExamName | MarksObtained | PreviousMarks | NextMarks | ChangeFromPrevious
Ravi Kumar  | Midterm  | 80            | NULL          | 92        | NULL
Ravi Kumar  | Final    | 92            | 80            | NULL      | +12
Priya Sharma| Midterm  | 75            | NULL          | 85        | NULL
Priya Sharma| Final    | 85            | 75            | NULL      | +10

LAG: Previous row value. LEAD: Next row value. NULL at boundaries.

Example 5: Top 3 per Subject (without GROUP BY)

Get top 3 scorers per subject:

SELECT 
    sub.Name AS Subject,
    s.Name AS Student,
    r.MarksObtained,
    RANK() OVER (
        PARTITION BY sub.SubjectId 
        ORDER BY r.MarksObtained DESC
    ) AS SubjectRank
FROM Subject sub
INNER JOIN Exam e ON sub.SubjectId = e.SubjectId
INNER JOIN ExamResult r ON e.ExamId = r.ExamId
INNER JOIN Student s ON r.StudentId = s.StudentId
ORDER BY sub.Name, SubjectRank
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

Actually to filter top 3 per subject, use CTE:

WITH RankedResults AS (
    SELECT 
        sub.Name AS Subject,
        s.Name AS Student,
        r.MarksObtained,
        RANK() OVER (
            PARTITION BY sub.SubjectId 
            ORDER BY r.MarksObtained DESC
        ) AS SubjectRank
    FROM Subject sub
    INNER JOIN Exam e ON sub.SubjectId = e.SubjectId
    INNER JOIN ExamResult r ON e.ExamId = r.ExamId
    INNER JOIN Student s ON r.StudentId = s.StudentId
)
SELECT * FROM RankedResults
WHERE SubjectRank <= 3
ORDER BY Subject, SubjectRank;

Result (top 3 per subject):

Subject | Student     | MarksObtained | SubjectRank
English | Priya Sharma| 95            | 1
English | Arjun Reddy | 92            | 2
English | Ravi Kumar  | 88            | 3
Maths   | Ravi Kumar  | 98            | 1
Maths   | Arjun Reddy | 95            | 2
Maths   | Kiran Rao   | 92            | 3

Window function in CTE. Filter WHERE rank <= 3.

Example 6: Average within Partition

Class average vs student performance:

SELECT 
    s.Name AS Student,
    s.ClassName,
    r.MarksObtained,
    AVG(r.MarksObtained) OVER (
        PARTITION BY s.ClassName
    ) AS ClassAverage,
    r.MarksObtained - AVG(r.MarksObtained) OVER (
        PARTITION BY s.ClassName
    ) AS AboveClassAverage
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
ORDER BY s.ClassName, r.MarksObtained DESC;

Result (comparison):

Student     | ClassName | MarksObtained | ClassAverage | AboveClassAverage
Ravi Kumar  | 10-A      | 92            | 85.0         | +7.0
Priya Sharma| 10-A      | 85            | 85.0         | 0.0
Sneha Patel | 10-A      | 78            | 85.0         | -7.0
Arjun Reddy | 10-B      | 88            | 82.5         | +5.5

AVG() OVER: Class average per student. Allows comparison.

---

Common Window Function Mistakes

Mistake 1: Missing PARTITION BY (ranks entire dataset)

Wrong:

SELECT 
    s.Name,
    s.ClassName,
    r.MarksObtained,
    ROW_NUMBER() OVER (ORDER BY r.MarksObtained DESC) AS Rank
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;
-- No PARTITION BY: Ranks globally (10-A and 10-B mixed)

Result: Top 3 across all classes, not per class.

Fix: Add PARTITION BY:

ROW_NUMBER() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS Rank

Mistake 2: Window function in WHERE (invalid)

Wrong:

SELECT * FROM Student s
WHERE ROW_NUMBER() OVER (ORDER BY s.Name) = 1;
-- ERROR: Window functions not allowed in WHERE

Fix: Use CTE or subquery:

WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY s.Name) AS Rn
    FROM Student s
)
SELECT * FROM Numbered WHERE Rn = 1;

Mistake 3: LAG/LEAD with wrong PARTITION

Wrong:

SELECT 
    s.Name,
    r.MarksObtained,
    LAG(r.MarksObtained) OVER (ORDER BY e.ExamDate) AS PrevMarks
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
-- No PARTITION BY: Compares across students!
-- Ravi's exam compared to Priya's previous exam (wrong)

Result: Meaningless comparisons.

Fix: Partition by student:

LAG(r.MarksObtained) OVER (PARTITION BY s.StudentId ORDER BY e.ExamDate)

Mistake 4: Confusing RANK and ROW_NUMBER with ties

Wrong:

-- Want unique rank per row
SELECT 
    s.Name,
    RANK() OVER (ORDER BY r.MarksObtained DESC) AS Rank
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;
-- RANK: 1, 1, 3 (not 1, 2, 3)

Result: Gaps on tied scores.

Fix: Use ROW_NUMBER for unique:

ROW_NUMBER() OVER (ORDER BY r.MarksObtained DESC) AS Rank

---

Best Practices for Window Functions

1. Always include ORDER BY in OVER -- Determines window frame
2. Partition before ranking -- Usually PARTITION BY logical grouping
3. Use CTE for complex ranking filters -- Window functions can't go in WHERE
4. PARTITION BY same column as GROUP BY -- Consistent grouping
5. Test ties with RANK vs DENSE_RANK -- Know the difference
6. LAG/LEAD include PARTITION BY -- Compare within group, not across
7. Running totals need ORDER BY -- Determines cumulative order

---

🎯 Q1: What's the difference between ROW_NUMBER, RANK, DENSE_RANK?

ROW_NUMBER: Unique per row (gaps on any tie).

1, 2, 3, 4

RANK: Rank with gaps on ties.

1, 1, 3, 4  (skip rank 2 on tie)

DENSE_RANK: Rank without gaps.

1, 1, 2, 3

Use ROW_NUMBER for pagination. RANK for olympiad-style. DENSE_RANK for continuous ranking.

🎯 Q2: How do I get top 3 students per class?

Window function + CTE:

WITH Ranked AS (
    SELECT 
        s.Name, s.ClassName, r.MarksObtained,
        ROW_NUMBER() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS Rank
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
)
SELECT * FROM Ranked WHERE Rank <= 3;

Window function in CTE. Filter Rank in outer query.

Can't use ROW_NUMBER directly in WHERE.

🎯 Q3: How do I calculate a running total?

SUM() with ORDER BY in window:

SELECT 
    StudentId, PaidOn, Amount,
    SUM(Amount) OVER (PARTITION BY StudentId ORDER BY PaidOn) AS CumulativeAmount
FROM FeePayment
ORDER BY StudentId, PaidOn;

ORDER BY creates window frame: all rows up to current = cumulative sum.

Without ORDER BY: Window includes all rows (total, not running).

🎯 Q4: What's the difference between LAG and LEAD?

LAG: Previous row value.

LAG(Amount) OVER (ORDER BY Date) AS PreviousAmount

LEAD: Next row value.

LEAD(Amount) OVER (ORDER BY Date) AS NextAmount

Both NULL at boundaries (no previous for first, no next for last).

🎯 Q5: Can I use window functions in GROUP BY?

No:

SELECT ClassName, AVG(MarksObtained)
FROM Student s
GROUP BY ClassName;  -- Simple aggregate

Use window to compare to group average:

SELECT 
    s.Name, s.ClassName, r.MarksObtained,
    AVG(r.MarksObtained) OVER (PARTITION BY s.ClassName) AS ClassAvg
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;
-- Window: Per-row aggregate (class average shown on each row)

Different purposes. GROUP BY collapses rows. Window keeps all rows.

🎯 Q6: Can I use window functions in WHERE?

No:

WHERE ROW_NUMBER() OVER (...) = 1;  -- ERROR

Use CTE or subquery:

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Name) AS Rn
    FROM Student
)
SELECT * FROM Ranked WHERE Rn = 1;

Window functions evaluated after WHERE. Use CTE to apply window first, then filter.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Window functions for ranking and analytics in SQL Server. Try these prompts:

• "Show ROW_NUMBER: rank students within their class by marks"
• "What's the difference between RANK and DENSE_RANK on tied scores?"
• "How do I create a running total of fee payments per student?"
• "How do I use LAG to compare exam marks to previous exam?"
• "Quiz me: ask 5 questions about window functions, OVER, LAG/LEAD"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

25. CTEs (Common Table Expressions) -- Coming soon