ADVANCED SQL ADVANCED lesson-11

Window Functions

🕑 35 min 📚 Lesson 11 of 15 ✅ 5 quiz questions

Window functions compute a value across a set of related rows — the "window" — without collapsing those rows into groups like GROUP BY does. They unlock rankings, running totals, moving averages, and row-to-row comparisons in a single query.

Section 1

What Are Window Functions?

GROUP BY collapses rows into one row per group. Window functions add an extra computed column to every row while keeping all rows visible.

SQL

-- Without window function (collapses rows)
SELECT grade, AVG(age) FROM students GROUP BY grade;

-- With window function (keeps all rows + adds aggregate column)
SELECT name, grade, age,
  AVG(age) OVER (PARTITION BY grade) AS avg_age_in_grade
FROM students;

The OVER clause turns any aggregate function into a window function. PARTITION BY defines the group (partition), but all rows are still returned.

Section 2

PARTITION BY and ORDER BY in OVER

PARTITION BY splits rows into partitions (like GROUP BY, but without collapsing). ORDER BY inside OVER defines the order for ranking or running calculations.

SQL

SELECT name, grade, age,
  ROW_NUMBER() OVER (PARTITION BY grade ORDER BY age DESC) AS rank_in_grade
FROM students;

Each student gets a rank within their grade group, ordered by age descending. The partition restarts the counter for each new grade.

Section 3

Ranking Functions

SQL provides four ranking functions that differ in how they handle ties:

SQL

SELECT name, score,
  ROW_NUMBER()   OVER (ORDER BY score DESC) AS row_num,   -- unique rank
  RANK()         OVER (ORDER BY score DESC) AS rank_val,  -- gaps for ties
  DENSE_RANK()   OVER (ORDER BY score DESC) AS dense_rank, -- no gaps
  NTILE(4)       OVER (ORDER BY score DESC) AS quartile   -- divide into 4 groups
FROM scores;

1️⃣

ROW_NUMBER

Assigns a unique sequential integer to every row — no ties

Unique

🏆

RANK

Ties share rank; next rank skips (1, 2, 2, 4)

Gaps

🥇

DENSE_RANK

Ties share rank; no skipping (1, 2, 2, 3)

No Gaps

📊

NTILE

Divides rows into N equal-sized buckets

Buckets

⬅️

LAG

Returns value from a previous row in the partition

Lookback

➡️

LEAD

Returns value from a following row in the partition

Lookahead

Section 4

LAG and LEAD — Row Comparisons

LAG(col, n, default) looks back n rows; LEAD(col, n, default) looks forward n rows. Perfect for period-over-period comparisons.

SQL

SELECT
  order_date,
  revenue,
  LAG(revenue,  1, 0) OVER (ORDER BY order_date) AS prev_revenue,
  LEAD(revenue, 1, 0) OVER (ORDER BY order_date) AS next_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS change
FROM monthly_sales;

The third argument to LAG/LEAD is a default value returned when there is no previous/next row (e.g., the first or last row).

Section 5

Running Totals with SUM OVER

Adding ORDER BY inside SUM() OVER creates a running (cumulative) total. You can also define a row frame for moving averages.

SQL

SELECT order_date, amount,
  SUM(amount)  OVER (ORDER BY order_date)                    AS running_total,
  AVG(amount)  OVER (ORDER BY order_date ROWS 6 PRECEDING)   AS `7day_avg`
FROM daily_sales;

ℹ️

Execution Order

Window functions run AFTER WHERE, GROUP BY, and HAVING — they operate on the final result set, not the raw table data.

🏻 Knowledge Check — Window Functions

1. What does PARTITION BY do in a window function?

Correct! PARTITION BY divides rows into partitions for the window function to operate on, but all rows remain in the output.

2. What is the difference between RANK and DENSE_RANK?

Correct! If two rows tie at rank 2, RANK gives the next row rank 4 (gap); DENSE_RANK gives it rank 3 (no gap).

3. LAG() accesses…

Correct! LAG(col, n) looks back n rows within the partition. LEAD(col, n) looks forward n rows.

4. ROW_NUMBER() guarantees…

Correct! ROW_NUMBER always produces unique sequential integers — it breaks ties arbitrarily if values are equal.

5. SUM() OVER (ORDER BY date) calculates…

Correct! Adding ORDER BY inside SUM() OVER creates a cumulative (running) total — the sum grows with each successive row.