SQL Window Functions: The Analyst's Complete Guide

If you are writing SQL for data analysis and you are not using window functions, you are doing it the hard way. Window functions let you calculate rankings, running totals, month-over-month comparisons, and moving averages — all without subqueries, self-joins, or temporary tables.

This guide covers every major window function with real business use cases — the kind you actually face when building sales reports, MIS dashboards, and financial analyses.

📋

Compatibility

All examples use standard SQL that works in SQL Server (T-SQL), PostgreSQL, MySQL 8+, BigQuery, and Snowflake. Minor syntax differences are noted where they exist.

1. What Are Window Functions?

A window function performs a calculation across a set of rows that are related to the current row — called a window. Unlike GROUP BY which collapses rows into one summary row, a window function keeps every row and adds the calculated result as a new column.

SQL — GROUP BY vs Window Function

-- GROUP BY: collapses rows — you lose individual sale detail
SELECT Region, SUM(Revenue) AS TotalRevenue
FROM FactSales
GROUP BY Region;

-- Window Function: keeps every row AND adds the regional total
SELECT
    OrderID,
    Region,
    Revenue,
    SUM(Revenue) OVER (PARTITION BY Region) AS RegionTotal,
    Revenue / SUM(Revenue) OVER (PARTITION BY Region) * 100 AS PctOfRegion
FROM FactSales;

The second query returns every order row with two extra columns — the region's total revenue and each order's percentage contribution. That would require a subquery or CTE with GROUP BY — window functions do it in one clean pass.

2. The OVER() Clause — Core Syntax

Every window function uses the OVER() clause. Understanding its three components is the key to mastering all window functions:

SQL — OVER() Syntax

function_name() OVER (
    PARTITION BY column1, column2   -- divide rows into groups (optional)
    ORDER BY     column3 DESC       -- sort within each group (optional)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- frame (optional)
)

-- PARTITION BY : defines the window (like GROUP BY but keeps all rows)
-- ORDER BY     : sort order within the window (required for ranking & LAG/LEAD)
-- ROWS BETWEEN : defines how many rows to include in the calculation (frame)

3. ROW_NUMBER — Assign a Unique Row Number

ROW_NUMBER() assigns a unique sequential number to each row within a partition. Ties get different numbers — it never repeats.

SQL — ROW_NUMBER()

-- Basic: number all rows by revenue (highest first)
SELECT
    OrderID, CustomerName, Revenue,
    ROW_NUMBER() OVER (ORDER BY Revenue DESC) AS RowNum
FROM FactSales;

-- Partitioned: number rows separately within each region
SELECT
    OrderID, Region, Revenue,
    ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS RegionRowNum
FROM FactSales;

-- Business use case: Get the LATEST order for each customer
WITH Numbered AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM FactSales
)
SELECT * FROM Numbered WHERE rn = 1;

-- Business use case: Remove duplicates keeping the most recent record
WITH Dedup AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY UpdatedAt DESC) AS rn
    FROM CustomerTable
)
DELETE FROM Dedup WHERE rn > 1;

4. RANK and DENSE_RANK — Handle Ties Correctly

When two rows have the same value, the ranking behaviour differs between these two functions:

Revenue	ROW_NUMBER	RANK	DENSE_RANK
₹95,000	1	1	1
₹82,000	2	2	2
₹82,000	3	2	2
₹71,000	4	4 (gap!)	3 (no gap)
₹60,000	5	5	4

SQL — RANK vs DENSE_RANK

SELECT
    ProductName,
    Revenue,
    RANK()       OVER (ORDER BY Revenue DESC) AS Revenue_Rank,
    DENSE_RANK() OVER (ORDER BY Revenue DESC) AS Revenue_DenseRank,
    ROW_NUMBER() OVER (ORDER BY Revenue DESC) AS Revenue_RowNum
FROM ProductSales;

-- Business use case: Top 3 products per category (with ties)
WITH Ranked AS (
    SELECT
        Category,
        ProductName,
        Revenue,
        DENSE_RANK() OVER (PARTITION BY Category ORDER BY Revenue DESC) AS dr
    FROM ProductSales
)
SELECT * FROM Ranked WHERE dr <= 3;

-- Business use case: Bottom 5 customers by order count
WITH Ranked AS (
    SELECT
        CustomerName,
        COUNT(OrderID) AS OrderCount,
        RANK() OVER (ORDER BY COUNT(OrderID) ASC) AS rnk
    FROM FactSales
    GROUP BY CustomerName
)
SELECT * FROM Ranked WHERE rnk <= 5;

💡

Which to Use — RANK or DENSE_RANK?

Use DENSE_RANK when you want to find the "Top N" — no gaps means your Top 3 always returns at least 3 distinct ranks. Use RANK when the gap matters — e.g., "finished 4th" in a competition where two people tied for 2nd genuinely means no one finished 3rd.

5. NTILE — Divide into Percentile Buckets

NTILE(n) divides rows into n equal buckets. This is perfect for customer segmentation, product performance quartiles, and cohort analysis.

SQL — NTILE()

-- Divide customers into 4 quartiles by revenue
SELECT
    CustomerName,
    TotalRevenue,
    NTILE(4) OVER (ORDER BY TotalRevenue DESC) AS Quartile
FROM CustomerRevenue;
-- Quartile 1 = top 25% customers (highest revenue)
-- Quartile 4 = bottom 25% customers (lowest revenue)

-- Business use case: Customer segmentation (10 deciles)
SELECT
    CustomerID,
    CustomerName,
    TotalRevenue,
    NTILE(10) OVER (ORDER BY TotalRevenue DESC) AS Decile,
    CASE NTILE(10) OVER (ORDER BY TotalRevenue DESC)
        WHEN 1  THEN 'Platinum'
        WHEN 2  THEN 'Gold'
        WHEN 3  THEN 'Silver'
        ELSE         'Standard'
    END AS CustomerTier
FROM CustomerRevenue;

-- Business use case: Product performance quartile by category
SELECT
    Category,
    ProductName,
    Revenue,
    NTILE(4) OVER (PARTITION BY Category ORDER BY Revenue DESC) AS PerfQuartile
FROM ProductSales;

6. LAG and LEAD — Access Previous and Next Row Values

LAG() looks backward — it fetches the value from a previous row. LEAD() looks forward — it fetches the value from a future row. These are the go-to functions for month-over-month comparisons and trend analysis.

SQL — LAG() and LEAD()

-- Syntax: LAG(column, offset, default_if_null)

-- Month-over-Month revenue comparison
SELECT
    YearMonth,
    Revenue,
    LAG(Revenue, 1, 0)  OVER (ORDER BY YearMonth) AS PrevMonthRevenue,
    Revenue - LAG(Revenue, 1, 0) OVER (ORDER BY YearMonth) AS MoM_Change,
    ROUND(
        (Revenue - LAG(Revenue, 1, 0) OVER (ORDER BY YearMonth))
        / NULLIF(LAG(Revenue, 1, 0) OVER (ORDER BY YearMonth), 0) * 100
    , 2) AS MoM_Pct
FROM MonthlySales
ORDER BY YearMonth;

-- Year-over-Year comparison (offset = 12 months)
SELECT
    YearMonth,
    Revenue,
    LAG(Revenue, 12, NULL) OVER (ORDER BY YearMonth) AS SamePeriodLastYear,
    ROUND(
        (Revenue - LAG(Revenue, 12, NULL) OVER (ORDER BY YearMonth))
        / NULLIF(LAG(Revenue, 12, NULL) OVER (ORDER BY YearMonth), 0) * 100
    , 2) AS YoY_Pct
FROM MonthlySales;

-- Per-customer: days between consecutive orders
SELECT
    CustomerID,
    OrderDate,
    LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrderDate,
    DATEDIFF(DAY,
        LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate),
        OrderDate
    ) AS DaysBetweenOrders
FROM FactSales;

-- LEAD: Flag orders that were followed by a return
SELECT
    OrderID,
    CustomerID,
    OrderType,
    LEAD(OrderType) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderType
FROM FactSales;

7. Running Totals and Moving Averages

Aggregate functions like SUM, COUNT, and AVG become incredibly powerful when combined with window frames. The frame clause (ROWS BETWEEN) defines exactly which rows are included in each calculation.

SQL — Running Totals & Moving Averages

-- ── Running Total ───────────────────────────────────────
SELECT
    OrderDate,
    Revenue,
    SUM(Revenue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM DailySales;

-- ── Running Total per Region ─────────────────────────────
SELECT
    Region,
    OrderDate,
    Revenue,
    SUM(Revenue) OVER (
        PARTITION BY Region
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RegionRunningTotal
FROM DailySales;

-- ── 3-Month Moving Average ───────────────────────────────
SELECT
    YearMonth,
    Revenue,
    AVG(Revenue) OVER (
        ORDER BY YearMonth
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3M
FROM MonthlySales;

-- ── 7-Day Moving Average (trend smoothing) ───────────────
SELECT
    SaleDate,
    DailyRevenue,
    AVG(DailyRevenue) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS Avg7Day
FROM DailySales;

-- ── Cumulative % of total ────────────────────────────────
SELECT
    ProductName,
    Revenue,
    SUM(Revenue) OVER (ORDER BY Revenue DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeRevenue,
    SUM(Revenue) OVER () AS GrandTotal,
    ROUND(
        SUM(Revenue) OVER (ORDER BY Revenue DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        / SUM(Revenue) OVER () * 100
    , 2) AS CumulativePct
FROM ProductSales;

💡

Frame Clause Reference

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — from first row to current (running total). ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — current row plus 2 rows before (3-period moving average). ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — all rows in partition (same as omitting the frame).

8. FIRST_VALUE and LAST_VALUE

SQL — FIRST_VALUE() and LAST_VALUE()

-- First sale date for each customer (baseline)
SELECT
    CustomerID,
    OrderDate,
    Revenue,
    FIRST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
    ) AS FirstOrderDate,
    FIRST_VALUE(Revenue) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
    ) AS FirstOrderRevenue
FROM FactSales;

-- Difference from first order in the region
SELECT
    Region,
    YearMonth,
    Revenue,
    FIRST_VALUE(Revenue) OVER (PARTITION BY Region ORDER BY YearMonth) AS BaseRevenue,
    Revenue - FIRST_VALUE(Revenue) OVER (PARTITION BY Region ORDER BY YearMonth) AS VsBaseline
FROM MonthlySales;

-- LAST_VALUE requires explicit frame — default frame cuts off early
SELECT
    CustomerID,
    OrderDate,
    LAST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastOrderDate
FROM FactSales;

⚠️

LAST_VALUE Gotcha

Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using LAST_VALUE(). Without it, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which makes LAST_VALUE return the current row's value, not the partition's last value.

9. PERCENT_RANK and CUME_DIST

SQL — PERCENT_RANK() and CUME_DIST()

-- PERCENT_RANK: relative rank as a % (0 = lowest, 1 = highest)
SELECT
    SalesPersonName,
    Revenue,
    ROUND(PERCENT_RANK() OVER (ORDER BY Revenue) * 100, 1) AS PercentileRank
FROM SalesPersonRevenue;
-- A salesperson at 85.0 is in the 85th percentile

-- CUME_DIST: % of rows with value ≤ current row
SELECT
    ProductName,
    Revenue,
    ROUND(CUME_DIST() OVER (ORDER BY Revenue) * 100, 1) AS CumulativeDist
FROM ProductSales;

-- Business use case: Flag top 20% performers
WITH Ranked AS (
    SELECT
        SalesPersonID,
        SalesPersonName,
        Revenue,
        PERCENT_RANK() OVER (ORDER BY Revenue DESC) AS prank
    FROM SalesPersonRevenue
)
SELECT *, CASE WHEN prank <= 0.20 THEN 'Top 20%' ELSE 'Standard' END AS Tier
FROM Ranked;

10. Real Business Use Cases

Use Case 1 — Sales Dashboard: MoM Growth by Region

SQL — MoM Growth Report

SELECT
    Region,
    YearMonth,
    Revenue,
    LAG(Revenue) OVER (PARTITION BY Region ORDER BY YearMonth) AS PrevMonth,
    ROUND(
        (Revenue - LAG(Revenue) OVER (PARTITION BY Region ORDER BY YearMonth))
        / NULLIF(LAG(Revenue) OVER (PARTITION BY Region ORDER BY YearMonth), 0) * 100
    , 2) AS MoM_Growth_Pct,
    SUM(Revenue) OVER (
        PARTITION BY Region
        ORDER BY YearMonth
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTD_Revenue
FROM RegionalMonthlySales
ORDER BY Region, YearMonth;

Use Case 2 — Inventory: Flag Slow-Moving Stock

SQL — Inventory Analysis

WITH ProductRanked AS (
    SELECT
        Category,
        ProductName,
        TotalSold,
        AvgDailySales,
        StockOnHand,
        DENSE_RANK() OVER (PARTITION BY Category ORDER BY TotalSold DESC) AS SalesRank,
        NTILE(4)     OVER (PARTITION BY Category ORDER BY TotalSold DESC) AS SalesQuartile,
        ROUND(StockOnHand / NULLIF(AvgDailySales, 0), 0) AS DaysOfStock
    FROM InventorySummary
)
SELECT *,
    CASE
        WHEN SalesQuartile = 4 AND DaysOfStock > 90 THEN 'Slow Mover — Action Required'
        WHEN SalesQuartile = 4 AND DaysOfStock > 60 THEN 'Watch List'
        WHEN SalesQuartile = 1 AND DaysOfStock < 14 THEN 'Risk of Stockout'
        ELSE 'Normal'
    END AS StockAlert
FROM ProductRanked
ORDER BY Category, SalesRank;

Use Case 3 — Customer Churn: Gap Between Orders

SQL — Customer Engagement Analysis

WITH OrderGaps AS (
    SELECT
        CustomerID,
        CustomerName,
        OrderDate,
        Revenue,
        LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrderDate,
        DATEDIFF(DAY,
            LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate),
            OrderDate
        ) AS DaysSincePrevOrder,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM FactSales
),
LastOrder AS (
    SELECT *,
        DATEDIFF(DAY, OrderDate, GETDATE()) AS DaysSinceLastOrder
    FROM OrderGaps WHERE rn = 1
)
SELECT
    CustomerID,
    CustomerName,
    OrderDate AS LastOrderDate,
    DaysSinceLastOrder,
    AVG(DaysSincePrevOrder) OVER (PARTITION BY CustomerID) AS AvgOrderFrequencyDays,
    CASE
        WHEN DaysSinceLastOrder > 180 THEN 'Churned'
        WHEN DaysSinceLastOrder > 90  THEN 'At Risk'
        WHEN DaysSinceLastOrder > 45  THEN 'Dormant'
        ELSE 'Active'
    END AS EngagementStatus
FROM LastOrder
ORDER BY DaysSinceLastOrder DESC;

Use Case 4 — Financial Report: Running P&L

SQL — Monthly P&L with Running Totals

SELECT
    FiscalYear,
    FiscalMonth,
    MonthName,
    Revenue,
    COGS,
    Revenue - COGS                                    AS GrossProfit,
    ROUND((Revenue - COGS) / NULLIF(Revenue,0)*100,1) AS GrossMarginPct,

    -- Year-to-date running totals
    SUM(Revenue) OVER (
        PARTITION BY FiscalYear ORDER BY FiscalMonth
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTD_Revenue,

    SUM(Revenue - COGS) OVER (
        PARTITION BY FiscalYear ORDER BY FiscalMonth
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTD_GrossProfit,

    -- Same period last year
    LAG(Revenue,    12) OVER (ORDER BY FiscalYear, FiscalMonth) AS SPLY_Revenue,
    LAG(Revenue-COGS,12) OVER (ORDER BY FiscalYear, FiscalMonth) AS SPLY_GrossProfit,

    -- 3-month moving average
    AVG(Revenue) OVER (
        ORDER BY FiscalYear, FiscalMonth
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3M

FROM MonthlyPL
ORDER BY FiscalYear, FiscalMonth;

11. Performance Tips

Index your PARTITION BY and ORDER BY columns — window functions sort data internally. An index on the partition and order columns dramatically reduces this overhead on large tables.
Use CTEs to avoid repeating the OVER() clause — if you reference the same window function more than once, compute it once in a CTE and reference the result.
Avoid multiple OVER() clauses with different ORDER BY — each unique OVER() definition triggers a separate sort pass. Minimise distinct window definitions in a single query.
Filter before windowing — apply WHERE conditions in a CTE before the window function. This reduces the row count the window operates over.
Prefer ROWS BETWEEN over RANGE BETWEEN — RANGE-based frames are harder for the engine to optimise. ROWS-based frames are more predictable and usually faster.

SQL — Performance Pattern

-- ❌ Slow: window function on full table, then filter
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM FactSales    -- runs on ALL 15 million rows
) t WHERE rn = 1;

-- ✅ Fast: filter to last 12 months first, then window
WITH Recent AS (
    SELECT * FROM FactSales
    WHERE OrderDate >= DATEADD(MONTH, -12, GETDATE())  -- filter FIRST
),
Numbered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Recent    -- window runs on fewer rows
)
SELECT * FROM Numbered WHERE rn = 1;

12. Quick Reference — All Window Functions

Function	Purpose	ORDER BY Required?
`ROW_NUMBER()`	Unique sequential number — no ties	Yes
`RANK()`	Rank with gaps on ties	Yes
`DENSE_RANK()`	Rank without gaps on ties	Yes
`NTILE(n)`	Divide rows into n equal buckets	Yes
`LAG(col, n)`	Value from n rows before	Yes
`LEAD(col, n)`	Value from n rows after	Yes
`FIRST_VALUE(col)`	First value in the window partition	Yes
`LAST_VALUE(col)`	Last value in the window partition	Yes
`PERCENT_RANK()`	Relative rank as 0–1 fraction	Yes
`CUME_DIST()`	Cumulative distribution 0–1	Yes
`SUM() OVER()`	Running / partitioned total	Optional
`AVG() OVER()`	Running / moving average	Optional
`COUNT() OVER()`	Running / partitioned count	Optional
`MIN() OVER()`	Running / partitioned minimum	Optional
`MAX() OVER()`	Running / partitioned maximum	Optional

🚀

Window Functions in Power BI

If your Power BI report uses DirectQuery, these SQL window functions run directly in your database — often faster than equivalent DAX. For Import Mode, use DAX equivalents: RANKX for rankings, CALCULATE + FILTER for running totals, and DATEADD for LAG/LEAD style comparisons.

Ankit Kumar

SQL Developer · Data Analyst · ETLGuru.in

Ankit Kumar is a Data Analyst and founder of Pyivot Solutions with 5+ years of experience writing production SQL for analytics, MIS reporting, and ETL pipelines across manufacturing, trading, and e-commerce businesses in India.

About LinkedIn GitHub Contact

SQL Window Functions: The Analyst's Complete Guide

1. What Are Window Functions?

2. The OVER() Clause — Core Syntax

3. ROW_NUMBER — Assign a Unique Row Number

4. RANK and DENSE_RANK — Handle Ties Correctly

5. NTILE — Divide into Percentile Buckets

6. LAG and LEAD — Access Previous and Next Row Values

7. Running Totals and Moving Averages

8. FIRST_VALUE and LAST_VALUE

9. PERCENT_RANK and CUME_DIST

10. Real Business Use Cases

Use Case 1 — Sales Dashboard: MoM Growth by Region

Use Case 2 — Inventory: Flag Slow-Moving Stock

Use Case 3 — Customer Churn: Gap Between Orders

Use Case 4 — Financial Report: Running P&L

11. Performance Tips

12. Quick Reference — All Window Functions

Ankit Kumar

Related Articles

10 SQL Optimization Tricks Every Analyst Must Know

25 DAX Measures Every Power BI Developer Needs

Python Pandas: Data Cleaning for Analysts