Lesson 2 of 5 · reading 15–25 minutes; practice and project time is on the module page

Window functions

🎯You'll be able to

Rank rows within groups, compute running totals and moving averages, and compare each row with the one before, using window functions.

🏠The everyday picture

In a class results list, each student keeps their own row, but you can add their rank within their section and the section's average next to them. Nothing is collapsed into one row per section.

📘What it really is

A window function calculates over a set of rows related to the current row, without collapsing them like group by does.
The window is set with over (partition by ... order by ... rows between ...):
partition by store: a separate window per store;
order by order_date: the order inside the window;
rows between 6 preceding and current row: a frame of the last 7 rows.
Common functions:
ranking: row_number() (1, 2, 3, ties broken arbitrarily), rank() (1, 1, 3), dense_rank() (1, 1, 2);
running totals: sum(x) over (order by day);
moving averages: avg(x) over (order by day rows between 6 preceding and current row);
changes: lag(x) and lead(x) give the previous or next row's value.
Top N per group: rank in a CTE, then filter where rk <= 3 in the next step (window results can't be filtered in the same where). Some engines also offer qualify.

📝A worked example

Input:

with s as (
  select store, product_key, sum(net_amount_paise) as rev from marts.fct_order_lines group by 1, 2
), r as (
  select *, rank() over (partition by store order by rev desc) as rk from s
)
select store, product_key, rev / 100 as rupees, rk from r where rk <= 3 order by store, rk;

Expected output: nine rows. Sugar 1 kg (SKU-138) is first in all three stores; Hadapsar's top three are Sugar, Groundnut oil (SKU-137) and Atta 5 kg (SKU-135). In the practice data, staples lead because their packs cost more.

🌍Where it's used

Retail: top products per store, share of category.
Finance: running balances and month-to-date totals.
Operations: 7-day moving averages to smooth daily noise.

📝Case scenario

A food chain's "top dish per outlet" report used row_number(). Two dishes tied at one outlet, and the report showed a different winner each day, depending on how the database happened to sort them. rank() showed both, and the outlet manager stopped asking why the winner kept changing.

🖱️Try it

Write a query with lag showing each day's net revenue and the change from the day before, plus a 7-day moving average. Success: for 30 October your change is +₹26,716 and the 7-day average about ₹63,947.

⚠️Common mistakes

row_number() when ties matter. Sign: winners change between runs. Fix: rank(), or add a tie-breaker to the order by.
Filtering a window result in the same step. Sign: an error, or a filter applied before ranking. Fix: rank in a CTE, filter after.
Moving averages over missing days. Sign: "7 rows" covers 9 days when two are missing. Fix: join to a date dimension first.

🛠️For your project

The dashboard's daily chart could add a 7-day moving average; Project 3's tests don't require it.

✅Quick check

How is a window function different from group by?

Show the answer

It adds a calculated value to each row instead of collapsing rows.

What's the difference between rank() and row_number()?

Show the answer

rank() gives ties the same number; row_number() breaks ties arbitrarily.