Window Functions

What Are SQL Window Functions?

Think of a window function as a way to look at a “slice” of data while still being part of the full spreadsheet (or table). You don’t change the rows; instead, you perform some calculation by looking at them together.

Real-Life Analogy

Imagine you’re a teacher with a gradebook for all students in a class. Each row is a student’s record, showing their name, subject, and score.

Now, suppose you want to:

• Find the average score for each subject.
• Rank students within their subject based on their score.
• Compare each student’s score to the highest score in their subject.

To do these tasks, you’d need to group and calculate, right? That’s exactly what a window function does—it lets you look at the rows “around” a specific row without grouping or losing details from the main table.

How It Works in SQL

When using window functions, you define a window, which is like saying, “I want to focus on this specific set of rows.” Then you apply a function (like average, rank, or sum) to that window.

Here’s the key:

1. PARTITION BY: Splits the data into groups (like splitting the gradebook by subjects).
2. ORDER BY: Orders the data within each group (like sorting by scores from highest to lowest).

Example (Using the Gradebook)

Let’s say you have this table:

Task 1: Find the Average Score Per Subject

Using a window function:
SELECT
Student,
Subject,
Score,
AVG(Score) OVER (PARTITION BY Subject) AS AvgScore
FROM Gradebook;


This adds a new column showing the average score for the subject without collapsing the rows.

Task 2: Rank Students by Score Within Their Subject

SELECT 
    Student,
    Subject,
    Score,
    RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) AS Rank
FROM Gradebook;