Lecture 09b: Window Functions & Analytical SQL

OVER Clause, PARTITION BY, Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK), Window Frames, and Offset Functions (LEAD, LAG)

Lecture Source Slide Deck

Original Slides: Lecture 09b (37 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture09b.pdf

1. The Big Idea: Window Functions vs. Aggregation

Feature	Standard `GROUP BY` Aggregation	Analytical Window Function
Row Count	Collapses multiple rows into a single summary row per group.	Preserves every individual row in the final output.
Output Access	Can only output grouped or aggregated expressions.	Can display both granular row details AND aggregate benchmarks side-by-side.
Syntax	`GROUP BY column`	`OVER (PARTITION BY ... ORDER BY ...)`

-- Compare each instructor's salary against their department's average
SELECT 
    name,
    dept_name,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY dept_name), 2) AS dept_avg,
    salary - ROUND(AVG(salary) OVER (PARTITION BY dept_name), 2) AS diff_from_avg
FROM instructor;

2. Anatomy of the `OVER` Clause

FUNCTION() OVER (
    [PARTITION BY partition_column, ...]
    [ORDER BY sort_column [ASC|DESC], ...]
    [ROWS BETWEEN frame_start AND frame_end]
)

PARTITION BY: Divides the dataset into distinct subsets (windows). If omitted, the entire table is treated as one giant window.
ORDER BY: Dictates the logical sequence in which window calculations (running sums, ranks) process rows inside the partition.
ROWS BETWEEN: Defines the physical frame of rows evaluated for the current row.

3. Ranking Functions Compared

Suppose three instructors have salaries: [90000, 90000, 75000].

Name	Salary	`ROW_NUMBER()`	`RANK()`	`DENSE_RANK()`
Alice	90,000	1	1	1
Bob	90,000	2	1	1
Charlie	75,000	3	3 (skips 2)	2 (never skips)

ROW_NUMBER(): Generates a strict strictly increasing sequence 1, 2, 3, ... with no ties.
RANK(): Assigns duplicate ranks on ties, and leaves gaps in the subsequent numbers.
DENSE_RANK(): Assigns duplicate ranks on ties, but guarantees contiguous numbering without gaps.

4. The Canonical "Top-N Per Group" Problem

To find the top 2 highest-paid instructors in each department:

WITH RankedFaculty AS (
    SELECT 
        name,
        dept_name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY dept_name 
            ORDER BY salary DESC
        ) AS rank_in_dept
    FROM instructor
)
SELECT name, dept_name, salary, rank_in_dept
FROM RankedFaculty
WHERE rank_in_dept <= 2;

Rule: Window functions cannot be filtered directly in a WHERE clause because WHERE runs before window functions. A CTE or subquery is required.

5. Running Totals & Window Frames

-- Compute cumulative running total of department salary spend
SELECT 
    dept_name,
    name,
    salary,
    SUM(salary) OVER (
        PARTITION BY dept_name 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM instructor;

Frame Boundaries:

UNBOUNDED PRECEDING: The very first row of the partition.
n PRECEDING: n rows prior to the current row.
CURRENT ROW: The row currently being calculated.
n FOLLOWING: n rows after the current row.
UNBOUNDED FOLLOWING: The final row of the partition.

LAG(col, n): Accesses the value of col from n rows before the current row.
LEAD(col, n): Accesses the value of col from n rows after the current row.

-- Calculate student tot_cred progress relative to the student ranked immediately below
SELECT 
    name,
    tot_cred,
    LAG(tot_cred, 1) OVER (ORDER BY tot_cred DESC) AS higher_peer_cred,
    tot_cred - LAG(tot_cred, 1) OVER (ORDER BY tot_cred DESC) AS difference
FROM student;

7. Topic Practice Questions

SELECT 
    dept_name,
    name,
    salary,
    AVG(salary) OVER (
        PARTITION BY dept_name 
        ORDER BY salary 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3
FROM instructor;

WITH monthly_sales AS (
    SELECT 
        STRFTIME('%Y-%m', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY month
)
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 / 
        NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2
    ) AS growth_percentage
FROM monthly_sales;

Lecture 09b: Window Functions & Analytical SQL

1. The Big Idea: Window Functions vs. Aggregation

2. Anatomy of the `OVER` Clause

3. Ranking Functions Compared

4. The Canonical "Top-N Per Group" Problem

5. Running Totals & Window Frames

Frame Boundaries:

6. Offset Navigation Functions: `LAG` and `LEAD`

7. Topic Practice Questions

On this page

Lecture 09b: Window Functions & Analytical SQL

Q1: Write a query to compute a 3-row moving average of salary within each department.

Q2: When would you use DENSE_RANK() instead of RANK()?

Q3: Calculate period-over-period growth in orders using LAG().

On this page