CHAPTER 22
The feature that separates intermediate from advanced SQL.
🌟 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.
function_name() OVER (
PARTITION BY column -- divide rows into groups (optional)
ORDER BY column -- order within each group
ROWS/RANGE ... -- window frame (advanced, optional)
)SQL
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;The Top-N-per-group pattern — learn this by heart
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;The Top-N-per-group pattern — learn this by heart
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;| Function | Tie behaviour | Sequence: 100, 90, 90, 80 |
|---|---|---|
| ROW_NUMBER() | Arbitary | 1, 2, 3, 4 |
| RANK() | Same rank, skip next | 1, 2, 2, 4 (3 is skipped) |
| DENSE_RANK() | Same rank, no skip | 1, 2, 2, 3 (consecutive) |
SQL
SELECT order_id, order_date, total,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;Each order vs previous and next order
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.