SQL Window Functions Explained — RANK, LAG, LEAD, ROW_NUMBER with Real Examples (2026)

Window functions calculate values across related rows while preserving each row. Practise ranking, comparing consecutive records, and running totals.

Practise interview-style exercises with worked explanations. These are learning examples, not a verified record of questions asked by a named employer.

What Are Window Functions?

Definition: 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 per group, window functions return a value for every single row while still performing group-level calculations.

The OVER() Clause — Core Syntax

FUNCTION() OVER (
    PARTITION BY column   -- divide into groups (optional)
    ORDER BY column        -- sort within each group
    ROWS BETWEEN ...       -- define frame (optional)
)

-- Example: rank employees by salary within each department
SELECT
    emp_name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Ranking Functions — RANK, DENSE_RANK, ROW_NUMBER

-- Find the highest-paid employee in each department (exactly 1 per dept)
WITH ranked AS (
    SELECT
        emp_name, department, salary,
        ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT emp_name, department, salary
FROM ranked WHERE rn = 1;

LAG and LEAD — Compare with Adjacent Rows

Definition: LAG() accesses a value from a previous row in the result set. LEAD() accesses a value from a subsequent row. Both are used for time-series comparisons — month-over-month growth, day-over-day changes, sequential analysis.

-- Month-over-month revenue growth
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER(ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER(ORDER BY month))
        * 100.0
        / LAG(revenue, 1) OVER(ORDER BY month),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

Running Totals and Moving Averages

SELECT
    order_date,
    daily_revenue,
    -- Running total (cumulative sum)
    SUM(daily_revenue) OVER(ORDER BY order_date) AS cumulative_revenue,
    -- 7-day moving average
    AVG(daily_revenue) OVER(
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_sales;

Top Window Function Interview Questions

1. Find the top 2 customers by revenue in each city — ROW_NUMBER() OVER(PARTITION BY city ORDER BY revenue DESC), filter WHERE rn <= 2
2. Calculate month-over-month revenue growth % — LAG() OVER(ORDER BY month), formula: (current – previous) * 100.0 / previous
3. Compute a 7-day rolling average of daily active users — AVG() OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
4. Find users active on 3+ consecutive days — ROW_NUMBER trick: date – ROW_NUMBER() creates a group key for consecutive dates
5. Find the percentage each salesperson contributes to total sales — SUM(sales) / SUM(SUM(sales)) OVER() * 100