20

CHAPTER 20

Window Functions

The feature that separates intermediate from advanced SQL.

11

Learning Blocks

SQL

Interactive Queries

In This Chapter

Concepts you'll master

How window functions differ from GROUP BY

ROW_NUMBER, RANK, DENSE_RANK

The Top-N-per-group pattern — used everywhere

Running totals with SUM() OVER

LAG and LEAD — looking backward and forward

NTILE for percentile buckets

🌟 Think of it this way: GROUP BY puts everyone in separate rooms and gives you a summary per room. Window functions keep everyone in the same room, but give each person their own name tag showing their rank, their group's average, and how they compare. You get the detail AND the summary.

Syntax

SQL QUERY

function_name() OVER (
    PARTITION BY column   -- divide rows into groups (optional)
    ORDER BY column       -- order within each group
    ROWS/RANGE ...        -- window frame (advanced, optional)
)

18.1 - ROW_NUMBER per Department

SQL QUERY

SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
AS rank_in_dept
FROM employees
ORDER BY department, rank_in_dept;

18.2 - Top Earner per Department

SQL QUERY

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary
DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

18.2 - Top Earner per Department

SQL QUERY

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary
DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

18.4 - Running Total

SQL QUERY

SELECT order_id, order_date, total,
    SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

18.5 - LAG & LEAD

SQL QUERY

SELECT order_id, total,
    LAG(total) OVER (ORDER BY order_date) AS prev_total,
    LEAD(total) OVER (ORDER BY order_date) AS next_total
FROM orders;

💡 Engineering Insight: Window functions were designed specifically for analytics workloads. Netflix uses them for viewing streak calculations, Uber for surge pricing windows, Swiggy for peak-hour analysis. Every data engineering role at a FAANG-level company expects fluency with window functions.

✅ Interview Tip: 'Write a query to find the second highest salary in each department' is a top-5 SQL interview question at Amazon, Microsoft and Google. The answer: ROW_NUMBER() or DENSE_RANK() inside a CTE.

Exercise

Practice your SQL skills

SQL EDITOR

Interactive Query Environment

Press semicolon (;) to auto-run query

Tasks

Complete all SQL challenges

Assign a row number to each employee based on salary (highest first).

Current Task

Rank employees based on salary (highest first).

Assign row numbers within each department based on salary (highest first).

Rank employees within each department based on salary.

Show each employee's salary along with the average salary of all employees.

Need help solving this task?