You need the three best-selling products in every category, plus the monthly running total of revenue per customer. A GROUP BY collapses rows and throws away the detail you wanted to keep. Window functions solve exactly that problem: they compute across a set of related rows while leaving every row in place.

If you already write joins, subqueries and aggregations, you are ready. The syntax is small. The mental model is the hard part, and most analysts get comfortable after a handful of real queries.

The OVER Clause, Explained Without Jargon

Every window function has the same shape: a function, then OVER, then a definition of the window.

function_name() OVER (PARTITION BY group_column ORDER BY sort_column)

PARTITION BY splits the result into groups. Think of it as GROUP BY that does not collapse anything. ORDER BY decides the sequence inside each group, which matters for ranking and running totals. Leave PARTITION BY out and the whole result set becomes one window. Leave ORDER BY out of a ranking function and the order is undefined, so the numbers you get back are not reliable.

The window is evaluated after WHERE and GROUP BY, and before the final ORDER BY. That single fact explains why you cannot filter on a window function inside WHERE, and why the usual trick is to compute it in a subquery or a CTE and filter outside.

Ranking Rows Inside Groups

A recurring request: rank products by revenue within each category, keeping all rows.

SELECT category, product_name, revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM product_sales;

The three ranking functions look similar and behave differently:

• ROW_NUMBER() — returns 1, 2, 3, 4. Always unique, with ties broken arbitrarily unless you add a tiebreaker column to ORDER BY.
• RANK() — returns 1, 2, 2, 4. Ties share a rank, and positions are skipped.
• DENSE_RANK() — returns 1, 2, 2, 3. Ties share a rank with no gaps.

Reach for ROW_NUMBER when you need exactly N rows per group or when deduplicating. Reach for DENSE_RANK when the question is "which distinct revenue levels are in the top three", because RANK silently skips a level after a tie and ROW_NUMBER cuts a tie in half. Wrapping the query in a CTE and filtering WHERE rn <= 3 gives you a clean top-three list per category.

Running Totals and Cumulative Metrics

Cumulative sums are where window functions start paying rent. To track a customer's lifetime value as it builds:

SELECT customer_id, order_date, amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;

That frame clause is doing real work. Without it, the default frame for an ordered window is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which groups rows with identical ORDER BY values together. Two orders on the same date would each show the combined total, which is rarely what you want. Naming the frame explicitly, and adding order_id as a tiebreaker, makes the result deterministic.

Moving Averages That Reflect Real Dates

A seven-row moving average is a one-line change:

AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7_rows
FROM daily_revenue;

Be careful with the name: this averages the last seven rows, not the last seven calendar days. If weekends are missing from your data, the window quietly reaches back further in time, and the line you draw for stakeholders is misleading. Aggregate your raw table to one row per day first, and join it to a date spine so empty days exist as zeros. Then the seven-row window really is a seven-day window.

Comparing a Row With the Previous One

LAG and LEAD reach into neighbouring rows without a self-join, which is the main reason they replaced a lot of ugly SQL people used to write.

SELECT customer_id, order_date, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change
FROM orders;

For a growth percentage, guard the division with NULLIF: amount / NULLIF(prev_amount, 0) - 1. The first row of each partition returns NULL for LAG, which is correct and worth keeping in mind when you sum the results later. A common trap is LAST_VALUE, which by default stops its frame at the current row and therefore returns the current value. Give it ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING if you actually want the final value of the partition.

Habits That Prevent Most Mistakes

• Filter window results in an outer query or a CTE. Window functions are not allowed in WHERE.
• Deduplicate with ROW_NUMBER inside a CTE, then keep only rn = 1 in the outer query.
• Always add a tiebreaker such as an ID to ORDER BY. Ties are the source of most "the number changed overnight" bug reports.
• State your frame explicitly whenever ORDER BY is present.
• Remember that window functions usually force a sort. Partition and order columns that are indexed will keep larger tables responsive.
• Keep a personal snippet file with your running-total, dedupe and moving-average patterns. Reusing them beats retyping them.

Put It Into Practice This Week

Pick one table you know well — orders, sessions, tickets — and write three queries against it: a per-group ranking with DENSE_RANK, a cumulative total partitioned by the entity you care about, and a LAG comparison that shows change since the previous event. Run each one, look at the first few rows, and check the boundaries by hand. The edges are where mistakes hide: the first row of a partition, tied values, and gaps in dates.

Once those three patterns feel routine, add a filtered top-N query and a deduplication query. That small toolkit covers a surprising share of the analytics questions that land on your desk, and it is all the same OVER clause underneath.