What are Window Functions?

Window functions compute values over a window (a set of rows related to the current row) without collapsing the result set into groups like GROUP BY does.

Unlike GROUP BY, which collapses results, window functions let you see both the original rows AND computed values like rank, running totals, and lag/lead comparisons. They're the tool that separates junior analysts from senior ones.

When to use:

  • Rank products by price or rating per category
  • Calculate running totals of revenue over time
  • Find the previous order amount for each customer
  • Divide customers into quartiles by lifetime spend
  • Compute 7-day moving averages for trend analysis

ROW_NUMBER and PARTITION BY

ROW_NUMBER assigns a unique sequential number to each row within a partition, ordered by a column. Use it to rank items globally or within groups.

Q1: Rank Products by Price (Global)

Let's rank all products by price, highest first:

sql
Mouse Wireless | Electronics | 358.34 | 1
Smart Watch | Electronics | 349.05 | 2
USB-C Hub | Electronics | 316.64 | 3
(10 rows returned)

The OVER clause: Defines the window for the function. ORDER BY price DESC sorts the window, so ROW_NUMBER counts from highest to lowest price.

Q2: Rank Products Within Each Category

Now rank products by price, but separately for each category:

sql
Clean Code | Books | 49.34 | 1
Algorithms Illuminated | Books | 47.07 | 2
SQL in 10 Minutes | Books | 46.12 | 3
Mouse Wireless | Electronics | 358.34 | 1
(71 rows returned)

PARTITION BY category: Resets the rank to 1 for each new category. Clean Code is rank 1 in Books, and Mouse Wireless is rank 1 in Electronics.

Key insight: PARTITION BY divides the data into groups, and the window function operates within each group independently. Without PARTITION BY, the entire table is one window.

RANK vs DENSE_RANK

When products have the same price, RANK creates gaps. DENSE_RANK doesn't. Let's see the difference:

Q3: Compare RANK and DENSE_RANK

sql
Mouse Wireless | Electronics | 358.34 | 1 | 1
Smart Watch | Electronics | 349.05 | 2 | 2
USB-C Hub | Electronics | 316.64 | 3 | 3
(15 rows returned)

The difference:

  • RANK(): If two rows tie, both get rank 1, and the next rank is 3 (skips 2).
  • DENSE_RANK(): If two rows tie, both get rank 1, and the next rank is 2 (no gap).

Use RANK when you want to see gaps (like "top 3 products"); use DENSE_RANK when you want continuous numbering.

LAG and LEAD — Access Adjacent Rows

LAG accesses the previous row; LEAD accesses the next row. Perfect for comparing order-to-order changes or tracking sequences.

Q4: LAG — Previous Order Amount

For each customer, compare the current order to the previous one:

sql
139 | 1 | 2024-08-29 | 1296.16 | NULL | NULL
59 | 1 | 2024-11-14 | 708.31 | 1296.16 | -587.85
153 | 1 | 2025-03-03 | 647.16 | 708.31 | -61.15
(300 rows returned)

Key points: LAG returns NULL for the first row (no previous order). The change column shows the difference from one order to the next.

Q5: LEAD — Days Until Next Order

Calculate the gap between each order and the next one:

sql
139 | 1 | 2024-08-29 | 2024-11-14 | 76 days, 19:36:21
59 | 1 | 2024-11-14 | 2025-03-03 | 108 days, 23:18:36
153 | 1 | 2025-03-03 | NULL | NULL
(300 rows returned)

Insight: LEAD returns NULL for the last row. Customer 1 waits 76 days between first and second order, then 108 days before the third.

Running Totals and Moving Averages

Use ROWS BETWEEN to define the window size. SUM over a growing window = running total. AVG over a fixed window = moving average.

Q6: Running Total of Delivered Orders

Track cumulative revenue from delivered orders over time:

sql
2024-01-01 | 929.12 | 929.12
2024-01-02 | 455.44 | 1384.56
2024-01-07 | 929.75 | 2314.31
(214 rows returned)

How it works: SUM(...) OVER (ORDER BY order_date) sums all rows from the beginning up to and including the current row. By 2024-01-07, cumulative delivered revenue is 2,314.31.

Q7: 7-Day Moving Average

Calculate a rolling 7-day average of daily revenue:

sql
2024-01-01 | 929.12 | 929.12
2024-01-02 | 455.44 | 692.28
2024-01-07 | 2162.63 | 1182.40
(177 rows returned)

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Includes the current row plus the 6 days before it (7 days total). This smooths out daily noise to reveal trends.

NTILE and PERCENT_RANK — Buckets and Percentiles

Divide data into equal groups (quartiles, deciles) or compute percentile rank.

Q8: NTILE — Customer Spend Quartiles

Divide customers into 4 groups by lifetime spend:

sql
Arjun Morrison | 4878.23 | 1
Liam Wilson | 4815.29 | 1
Sneha Reddy | 4736.27 | 1
(100 rows returned, 25 in each quartile)

NTILE(4): Creates 4 buckets. Top 25% of spenders = quartile 1, next 25% = quartile 2, etc. Perfect for VIP segmentation.

Q9: PERCENT_RANK — Percentile Position

Show each customer's percentile rank (0–100):

sql
Arjun Morrison | 4878.23 | 100.0
Liam Wilson | 4815.29 | 99.0
Sneha Reddy | 4736.27 | 98.0
(100 rows returned)

PERCENT_RANK(): Scores from 0 to 1, then multiply by 100. Arjun is in the 100th percentile (top spender), Sofia in the 93rd percentile, etc.

Top N Per Group — Classic Pattern

Find the top product in each category using ROW_NUMBER + CTE. This is a foundational analytics pattern used everywhere.

Q10: Best-Rated Product per Category

sql
Python Crash Course | Books | 29.74 | 4.98
Polo Shirt | Clothing | 81.34 | 4.72
Phone Charger 65W | Electronics | 253.18 | 4.83
Water Bottle 1L | Home & Kitchen | 47.96 | 5.00
Ankle Weights | Sports | 72.81 | 4.41
Puzzle 1000pc | Toys | 118.64 | 4.04
(6 rows returned)

The pattern:

  1. CTE + ROW_NUMBER: Partition by category, order by rating descending.
  2. WHERE rn = 1: Keep only the top row per category.
  3. Result: Best product in each category with one query.

Change WHERE rn = 1 to WHERE rn <= 3 to get the top 3 per category. This pattern is the foundation of competitive analysis, personalization, and business reporting.

Pro tip: NULLS LAST keeps NULL ratings at the bottom, so highest ratings come first.

Challenge

Find the top 2 customers (by lifetime_spend) in each spend quartile.

Hint: Use two CTEs — one to assign quartiles, one to rank within quartiles.

Show hint

Structure:

sql

Test Your Knowledge

Question 1 of 5
What does OVER() do in a window function?
Question 2 of 5
What's the difference between RANK and DENSE_RANK?
Question 3 of 5
What does LAG do?
Question 4 of 5
How do you create a running total?
Question 5 of 5
What does NTILE(4) do?
0
/5
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Window functions — now you think in analytics.

Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer →