SQL Window Functions Explained: 7 Powerful Techniques (With a Live Demo)

RANK() and DENSE_RANK() look almost identical until a tie shows up — and that one gap trips up more SQL window functions interviews than almost anything else. Instead of just describing the difference, here’s a live demo: same five rows, same query shape, different function — click through all seven and watch the result column actually change.

What Are SQL Window Functions?

A window function performs a calculation across a set of rows that are related to the current row — a “window” — without collapsing those rows into a single output the way GROUP BY does. That’s the entire idea in one sentence: a window function lets you rank, compare, or aggregate rows while still showing every individual row.

SELECT
    employee_name,
    department,
    sales_amount,
    RANK() OVER (
        PARTITION BY department
        ORDER BY sales_amount DESC
    ) AS dept_rank
FROM sales;

Compare that to a regular aggregate query with GROUP BY department — that would collapse every employee in a department into one row. The window function keeps every employee visible while still calculating something that depends on the whole department’s data. That distinction is the single most important thing to understand before memorizing any specific function.

PARTITION BY and ORDER BY, Explained Simply

PARTITION BY splits your data into groups — think of it as a GROUP BY that doesn’t collapse rows. ORDER BY (inside the OVER() clause) decides the sequence within each group, which matters enormously for ranking and offset functions, and somewhat less for simple aggregates.

... OVER (
    PARTITION BY department   -- groups: Electronics, Fashion
    ORDER BY sales_amount DESC -- highest sales first, within each group
)

Leave out PARTITION BY entirely, and the whole table is treated as one single window — useful when you want a running total or rank across everything, not per group.

The 7 Techniques, One at a Time

1. ROW_NUMBER()

Assigns a unique, sequential number to every row in the partition — 1, 2, 3, 4 — regardless of ties. Two employees with identical sales still get different numbers. Use this when you need a strict, arbitrary-but-unique ordering, like picking exactly one “first” row per group even when values are tied.

2. RANK()

Gives tied rows the same rank, then skips the next rank accordingly. If two employees tie for 1st, the next employee is ranked 3rd, not 2nd — the gap reflects how many rows were tied above it.

3. DENSE_RANK()

Also gives tied rows the same rank, but does not skip the next number. Two employees tied for 1st means the next employee is ranked 2nd. This is the single most common point of confusion in SQL interviews — RANK() leaves gaps, DENSE_RANK() doesn’t, and that’s the whole difference.

4. NTILE(n)

Splits each partition into n roughly equal buckets — NTILE(4) for quartiles, NTILE(2) for a simple top-half/bottom-half split. When a partition doesn’t divide evenly, the earlier buckets absorb the extra rows.

5. LAG()

Looks backward — it pulls a value from a previous row in the same partition, without needing a self-join. LAG(sales_amount, 1) returns the previous row’s sales figure, or NULL if there isn’t one (the first row in each partition).

6. LEAD()

The mirror image of LAG() — it looks forward to a following row. LEAD(sales_amount, 1) returns the next row’s value, or NULL for the last row in the partition. LAG/LEAD together are what make month-over-month or day-over-day comparisons possible without a self-join.

7. SUM() (and other aggregates) as Window Functions

The same aggregate functions you already know — SUM(), AVG(), COUNT(), MIN(), MAX() — become window functions the moment you add an OVER() clause. With an ORDER BY inside that clause, SUM() stops being a single total and becomes a running total instead.

Frame Clauses: The Detail That Actually Breaks Running Totals

By default, an ordered window function uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — and RANGE has a genuinely surprising behavior with ties: rows that share the same ORDER BY value are treated as one logical group and get the same cumulative result.

Using the same tied pair from the demo above (Aman and Priya, both at ₹90,000): under the default RANGE frame, both get a running total of ₹1,80,000 — not ₹90,000 then ₹1,80,000. If you actually want a strict row-by-row running total that doesn’t merge ties, you need to be explicit:

SUM(sales_amount) OVER (
    PARTITION BY department
    ORDER BY sales_amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

ROWS looks at physical row position, not value grouping — so it gives you ₹90,000 then ₹1,80,000 then ₹2,55,000, one row at a time, regardless of ties. This is exactly the difference the demo’s “SUM() Running Total” tab shows.

Common SQL Window Function Mistakes

• Using RANK() when you actually meant DENSE_RANK(), or vice versa. Always ask: should ties create a gap in the ranking, or not?
• Forgetting PARTITION BY entirely. Without it, your ranking or running total applies across the whole table, not per group — a very easy detail to miss.
• Assuming the default frame clause does a simple row-by-row running total. As shown above, it doesn’t when there are ties — RANGE merges them.
• Putting a window function result directly in a WHERE clause. Window functions are computed after WHERE, GROUP BY, and HAVING — to filter on one, wrap the query and filter in an outer SELECT (or use QUALIFY, on engines that support it).
• Confusing window functions with GROUP BY. If you need every individual row to still appear in the output, you need a window function, not an aggregate GROUP BY.

SQL Window Functions in Data Analyst Interviews

Expect to be asked to rank employees or products within a group, find the top N per category, calculate a running total, compare a row to the previous or next one, and explain RANK() vs DENSE_RANK() vs ROW_NUMBER() clearly. A very common practical one — top 2 salespeople per department:

SELECT * FROM (
    SELECT
        employee_name,
        department,
        sales_amount,
        RANK() OVER (
            PARTITION BY department
            ORDER BY sales_amount DESC
        ) AS dept_rank
    FROM sales
) ranked
WHERE dept_rank <= 2;

Notice the subquery — this is exactly the “window functions can’t go in WHERE directly” rule from the mistakes list above, applied in practice.

Practice These Yourself

Using any small sales or employee table you have access to, work through: ranking employees within department by sales, finding the top 3 per category using a subquery, calculating a running total with an explicit ROWS frame, comparing each month’s revenue to the previous month with LAG(), predicting next month’s expected pattern context with LEAD(), and splitting customers into quartiles with NTILE(4). Try predicting the output on paper before running each query — that’s the single fastest way to actually internalize how these functions differ.

Your Turn: Break Your Own Assumptions

Don’t just read the demo above — go find (or build) a tiny table with at least one tie in it, and run all seven functions against it yourself. The tie is where the real learning happens; anyone can get ROW_NUMBER() right on data with no duplicates. Getting RANK(), DENSE_RANK(), and the RANGE-vs-ROWS running total right on tied data is what actually separates “I’ve seen window functions before” from “I understand window functions.”

Frequently Asked Questions About SQL Window Functions

Conclusion: Window Functions Reward Precision, Not Memorization

Every one of these seven techniques answers a slightly different question about the same underlying idea: perform a calculation across related rows without losing any individual row from the output. The fastest way to actually learn the differences isn’t reading definitions — it’s finding a dataset with a tie in it and watching how RANK(), DENSE_RANK(), and a RANGE-based running total each handle that tie differently. Once that clicks, the rest of the syntax is just vocabulary.