SQL Window Functions: The Complete Guide for Analysts

If you can write GROUP BY but freeze when an interviewer asks "give me the running total" or "rank products within each category," you are missing the single most important intermediate SQL skill: window functions. They are what separate analysts who write reports from analysts who write queries that answer business questions. This complete guide takes you from zero to confident, using Flipkart, Zomato and UPI style data and queries you can run today.

The core idea: calculate without collapsing

GROUP BY summarises and collapses rows - ten orders become one row per category. That is great for totals, but terrible when you want to keep every order and show a calculation alongside it, like "this order's rank" or "the running total up to this order."

A window function performs a calculation across a set of rows (a "window") while keeping every row. The magic word is OVER.

SELECT
    order_id,
    category,
    order_value,
    SUM(order_value) OVER () AS total_revenue
FROM orders;

Every row keeps its detail, but now also carries the grand total. OVER () with empty parentheses means "the window is the whole result set." That alone is already useful - you can compute each order's share of total revenue without a self-join.

OVER, PARTITION BY, ORDER BY

The OVER clause has three levers. Learn what each does and you have learned 80% of window functions.

PARTITION BY - group the window

PARTITION BY splits the rows into groups and runs the calculation separately within each group, like a GROUP BY that does not collapse.

SELECT
    order_id,
    category,
    order_value,
    SUM(order_value) OVER (PARTITION BY category) AS category_revenue
FROM orders;

Now each Flipkart order shows the total revenue for its own category. Electronics rows get the electronics total, apparel rows get the apparel total - all without losing a single order row.

ORDER BY - sequence the window

Adding ORDER BY inside OVER turns a static aggregate into a running one. Order matters because the function accumulates as it walks down the sequence.

SELECT
    order_date,
    order_value,
    SUM(order_value) OVER (ORDER BY order_date) AS running_total
FROM orders;

This is a running total of revenue by date. Combine both levers for a running total within each category:

SELECT
    category,
    order_date,
    order_value,
    SUM(order_value) OVER (
        PARTITION BY category
        ORDER BY order_date
    ) AS running_total_in_category
FROM orders;

The big families of window functions

Ranking functions

These assign positions within a partition:

• ROW_NUMBER() - a unique number 1, 2, 3 with no ties.
• RANK() - ties share a rank, then it skips (1, 1, 3).
• DENSE_RANK() - ties share a rank, no skip (1, 1, 2).
• NTILE(n) - splits rows into n roughly equal buckets.

Find the top 3 products by revenue within each category:

SELECT *
FROM (
    SELECT
        category,
        product_name,
        SUM(order_value) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY SUM(order_value) DESC
        ) AS rnk
    FROM orders
    GROUP BY category, product_name
) ranked
WHERE rnk <= 3;

This top-N-per-group pattern is one of the most common things you will write as an analyst. Notice you must wrap the window function in a subquery to filter on it - window functions cannot go in a WHERE clause directly, because they are evaluated after WHERE.

Offset functions

LAG() and LEAD() reach to a previous or next row - perfect for month-on-month comparisons.

SELECT
    txn_month,
    upi_volume,
    LAG(upi_volume) OVER (ORDER BY txn_month) AS prev_month,
    upi_volume - LAG(upi_volume) OVER (ORDER BY txn_month) AS mom_change
FROM upi_monthly;

Aggregate functions as windows

SUM, AVG, COUNT, MIN, MAX all work with OVER. A 3-month moving average of UPI volume:

SELECT
    txn_month,
    upi_volume,
    AVG(upi_volume) OVER (
        ORDER BY txn_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM upi_monthly;

That ROWS BETWEEN clause is a frame - we cover it in depth below.

More worked examples you will actually use

The three levers above unlock most real reporting questions. Here are five worked patterns that show up again and again in Indian analytics teams.

Percent of total without a self-join

Marketing wants every Flipkart order to show what share of its category's revenue it represents. Put the per-order value over the partition total:

SELECT
    order_id,
    category,
    order_value,
    ROUND(
        100.0 * order_value
        / SUM(order_value) OVER (PARTITION BY category),
        2
    ) AS pct_of_category
FROM orders;

The 100.0 (not 100) forces floating-point division so you do not get truncated integers - a classic silent bug.

Running total of UPI value within each month

Treasury teams love a cumulative curve. Reset the running total at each month boundary with PARTITION BY:

SELECT
    txn_month,
    txn_date,
    upi_value,
    SUM(upi_value) OVER (
        PARTITION BY txn_month
        ORDER BY txn_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_value_in_month
FROM upi_daily;

Moving average of Zomato daily orders

A 7-day moving average smooths the weekday/weekend wobble in food-delivery demand:

SELECT
    order_date,
    daily_orders,
    AVG(daily_orders) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM zomato_daily;

Gaps between events with LAG

How many days pass between consecutive orders from the same Zomato customer? Subtract the previous order date from the current one, per customer:

SELECT
    customer_id,
    order_date,
    order_date - LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS days_since_prev_order
FROM zomato_orders;

The first order per customer returns NULL because there is no earlier row - exactly what you want for "this is their first order." This gap analysis is the foundation of churn signals and reorder-rate metrics.

Top spender per city with ROW_NUMBER

When you want strictly one row per group - no ties - reach for ROW_NUMBER instead of RANK:

SELECT city, customer_id, total_spend
FROM (
    SELECT
        city,
        customer_id,
        SUM(order_value) AS total_spend,
        ROW_NUMBER() OVER (
            PARTITION BY city
            ORDER BY SUM(order_value) DESC
        ) AS rn
    FROM orders
    GROUP BY city, customer_id
) t
WHERE rn = 1;

FIRST_VALUE, LAST_VALUE, NTH_VALUE and named windows

Sometimes you do not want a rank or a running sum - you want to pull a specific row's value into every row of the partition. That is what the value functions do.

SELECT
    category,
    product_name,
    order_value,
    FIRST_VALUE(product_name) OVER w  AS top_product,
    NTH_VALUE(product_name, 2) OVER w AS second_product
FROM orders
WINDOW w AS (
    PARTITION BY category
    ORDER BY order_value DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Two things to notice. First, the named window (WINDOW w AS (...)) lets you define the window once and reuse it across several functions - cleaner and less error-prone than copy-pasting the same OVER (...) three times. Second, the explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

That frame matters most for LAST_VALUE. With the default frame (RANGE ... CURRENT ROW), LAST_VALUE only sees rows up to the current one, so it returns the current row's value instead of the partition's true last value - the number-one "why is LAST_VALUE broken?" question online. Widen the frame and it behaves:

SELECT
    category,
    product_name,
    order_value,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category
        ORDER BY order_value DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_value_product
FROM orders;

Window frames: the part people skip

When you add ORDER BY to an aggregate window, SQL applies a default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default is why SUM ... ORDER BY gives a running total. You can override it to control exactly which rows feed the calculation.

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW - this row and the two before it (the moving average above).
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - everything up to now (running total).
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - this row to the end.
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING - a centred 3-row window.

The RANGE vs ROWS deep-dive

ROWS counts physical rows. RANGE groups rows with the same ORDER BY value together. The difference is invisible until your sort column has duplicates - then the two give different answers.

Imagine three Flipkart orders on 2026-06-01 worth 100, 200 and 300, then one order on 2026-06-02 worth 50.

SELECT
    order_date,
    order_value,
    SUM(order_value) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_rows,
    SUM(order_value) OVER (
        ORDER BY order_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_range
FROM orders;

For the three June-1 rows, running_rows climbs 100, 300, 600 because it counts each physical row as it goes. But running_range shows 600, 600, 600 for all three, because RANGE treats every row sharing the date 2026-06-01 as one peer group and jumps straight to their combined total. Both then reach 650 on June 2. For most analyst work, prefer explicit ROWS so tied values do not silently inflate your running totals.

Window functions in interviews

Interviewers love window functions because they reveal whether you really understand SQL evaluation order. Here are three classics with solutions.

1. Second highest order value per category

A LIMIT-based answer breaks the moment you need it per category. Rank within each partition and pick rank 2:

SELECT category, order_value
FROM (
    SELECT
        category,
        order_value,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY order_value DESC
        ) AS rnk
    FROM orders
) t
WHERE rnk = 2;

Use DENSE_RANK if "second highest" should mean the second distinct value even when the top value is tied.

2. Month-on-month growth percentage of UPI volume

Combine LAG with the percent-change formula:

SELECT
    txn_month,
    upi_volume,
    ROUND(
        100.0 * (upi_volume - LAG(upi_volume) OVER (ORDER BY txn_month))
        / LAG(upi_volume) OVER (ORDER BY txn_month),
        2
    ) AS mom_growth_pct
FROM upi_monthly;

3. Detect consecutive active days (a streak)

A neat trick: subtract ROW_NUMBER() from the date. Rows that are consecutive produce the same offset, so they group into a streak. This "gaps and islands" pattern is a favourite at product analytics interviews.

SELECT
    customer_id,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*) AS streak_length
FROM (
    SELECT
        customer_id,
        activity_date,
        activity_date - (ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY activity_date
        ) * INTERVAL '1 day') AS streak_group
    FROM user_activity
) t
GROUP BY customer_id, streak_group
ORDER BY customer_id, streak_start;

Pitfalls that catch beginners

• Filtering on a window result. You cannot use a window function in WHERE or GROUP BY. Wrap it in a subquery or CTE and filter outside.
• Forgetting the frame. A running total or LAST_VALUE that looks wrong is often the default RANGE frame colliding with duplicate sort values. Specify ROWS.
• PARTITION BY vs GROUP BY confusion. GROUP BY collapses; PARTITION BY does not. If you want one row per group, you still need GROUP BY.
• NULLs from LAG/LEAD. The first row has no previous row, so LAG returns NULL. Use LAG(col, 1, 0) to default to 0, or handle the NULL downstream.
• Performance. Window functions over huge unindexed tables can be slow. An index supporting the PARTITION BY and ORDER BY columns helps a lot.

FAQ

What is the difference between GROUP BY and window functions?

GROUP BY collapses many rows into one summary row per group, so you lose row-level detail. A window function computes across a group of rows but keeps every row, attaching the calculation as an extra column. Use GROUP BY for one-row-per-category reports; use window functions when you need the detail and the aggregate side by side.

Can I use a window function in WHERE?

No. Window functions are evaluated after WHERE, GROUP BY and HAVING, so the result does not exist yet when WHERE runs. Wrap the query in a subquery or CTE and filter on the window column in the outer query (as in the top-N examples above).

RANK vs DENSE_RANK - what is the difference?

Both give tied rows the same rank. RANK() then skips numbers, so two first-place ties produce 1, 1, 3. DENSE_RANK() does not skip, producing 1, 1, 2. Use DENSE_RANK when "the next distinct value" matters (like "second highest"), and ROW_NUMBER when you need strictly unique numbers with no ties at all.

Why does LAST_VALUE return the wrong value?

Because of the default frame. With ORDER BY present, the window only spans up to the current row, so LAST_VALUE returns the current row's value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make it see the whole partition.

Are window functions slow?

They can be on large tables, but they are almost always faster than the self-joins or correlated subqueries they replace. The biggest win comes from an index that matches your PARTITION BY and ORDER BY columns, which lets the database avoid an extra sort.

Do window functions work in MySQL?

Yes, from MySQL 8.0 onward. They have been standard in PostgreSQL, SQL Server, Oracle, BigQuery, Snowflake and Redshift for years. The syntax in this guide is standard SQL and runs in PostgreSQL with little or no change across dialects.

Why this matters for your career

Almost every real analytics question - top N per group, growth rates, cohort behaviour, running balances, customer segmentation - is a window function in disguise. Master OVER, PARTITION BY, ORDER BY, and frames, and you will solve in five lines what others attempt with messy self-joins and temporary tables. It is the highest-return SQL topic you can study right now.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.