SQL Window Functions for Data Analysts: ROW_NUMBER, RANK, and Running Totals Explained

Window Functions vs GROUP BY: The Core Difference

The Anatomy of the OVER() Clause

• PARTITION BY: Divides the dataset into logical calculation buckets (e.g., by region, department, or customer segment). If omitted, the entire table is treated as one partition.
• ORDER BY: Dictates the evaluation order within each partition (vital for rankings and running totals).
• Frame Clause (ROWS/RANGE): Defines the physical boundary of rows included in the window relative to the current row.

SELECT 
    order_id,
    customer_id,
    region,
    order_amount,
    -- Window calculation: Average order value within the specific region
    AVG(order_amount) OVER(
        PARTITION BY region
    ) AS avg_regional_spend
FROM orders;

Ranking: ROW_NUMBER vs RANK vs DENSE_RANK

• ROW_NUMBER(): Assigns strictly unique sequential numbers (1, 2, 3, 4). Useful for pagination or deduplication.
• RANK(): Assigns identical rank for ties (1, 2, 2, 4) and skips subsequent numbers.
• DENSE_RANK(): Assigns identical rank for ties without skipping (1, 2, 2, 3). Standard for executive leaderboard reports.

Comparing Prior Periods: LEAD and LAG

SELECT 
    sales_month,
    monthly_revenue,
    -- Retrieve previous month's revenue
    LAG(monthly_revenue, 1) OVER(
        ORDER BY sales_month
    ) AS prev_month_revenue,
    -- Compute percentage difference
    ROUND(
        ((monthly_revenue - LAG(monthly_revenue, 1) OVER(ORDER BY sales_month)) 
        / LAG(monthly_revenue, 1) OVER(ORDER BY sales_month)) * 100, 2
    ) AS mom_growth_pct
FROM monthly_sales_summary;

Calculating Cumulative Running Totals

SELECT 
    order_date,
    daily_revenue,
    -- Cumulative running sum within the fiscal year
    SUM(daily_revenue) OVER(
        PARTITION BY fiscal_year
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_revenue
FROM daily_revenue_ledger;

Common Mistakes Beginners Make

• Placing window functions in the WHERE clause: Window functions execute after WHERE and HAVING. To filter by rank (e.g., Top 3 per category), wrap the query in a Common Table Expression (CTE) or subquery.
• Forgetting ORDER BY in ranking: Running ROW_NUMBER() without an explicit ORDER BY produces non-deterministic, random rankings.
• Over-partitioning: Partitioning on too many high-cardinality columns fragments calculations and leads to misleading percentages.