SQL Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD Explained

SQL Window Functions

Window functions perform calculations across rows related to the current row, without collapsing them into groups like GROUP BY. They are one of the most powerful and underused features in SQL — and a favourite in senior SQL interviews.

ROW_NUMBER, RANK, DENSE_RANK

-- Setup
CREATE TABLE sales (
    id          INT,
    employee    VARCHAR(50),
    department  VARCHAR(50),
    amount      DECIMAL(10,2),
    sale_date   DATE
);

INSERT INTO sales VALUES
(1,'Rahul','Engineering',50000,'2026-01-15'),
(2,'Priya','Marketing',  75000,'2026-01-20'),
(3,'Amit', 'Engineering',50000,'2026-01-22'),
(4,'Neha', 'Marketing',  90000,'2026-01-25'),
(5,'Vikram','Engineering',80000,'2026-01-28');

-- ROW_NUMBER: unique sequential number (no ties)
-- RANK: same rank for ties, gaps after (1,1,3)
-- DENSE_RANK: same rank for ties, no gaps (1,1,2)
SELECT
    employee, department, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
    RANK()       OVER (ORDER BY amount DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales;

-- Result:
-- employee | amount | row_num | rank_num | dense_rank
-- ---------|--------|---------|----------|----------
-- Neha     | 90000  | 1       | 1        | 1
-- Vikram   | 80000  | 2       | 2        | 2
-- Priya    | 75000  | 3       | 3        | 3
-- Rahul    | 50000  | 4       | 4        | 4  ← ROW_NUMBER differs
-- Amit     | 50000  | 5       | 4        | 4  ← RANK/DENSE same

PARTITION BY — Rank Within Groups

-- Rank employees within each department
SELECT
    employee, department, amount,
    RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dept_rank
FROM sales;

-- Result: separate ranking per department
-- employee | department  | amount | dept_rank
-- ---------|-------------|--------|----------
-- Vikram   | Engineering | 80000  | 1
-- Rahul    | Engineering | 50000  | 2  ← tie
-- Amit     | Engineering | 50000  | 2  ← tie
-- Neha     | Marketing   | 90000  | 1  ← resets per dept!
-- Priya    | Marketing   | 75000  | 2

SUM, AVG with OVER

-- Running total
SELECT
    sale_date, employee, amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    AVG(amount) OVER (PARTITION BY department) AS dept_avg,
    amount - AVG(amount) OVER (PARTITION BY department) AS vs_dept_avg
FROM sales
ORDER BY sale_date;

-- Get top earner per department (common interview question!)
SELECT * FROM (
    SELECT *,
        RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rk
    FROM sales
) ranked
WHERE rk = 1;

LAG and LEAD

-- LAG: access previous row's value
-- LEAD: access next row's value
SELECT
    sale_date, employee, amount,
    LAG(amount, 1, 0)  OVER (PARTITION BY department ORDER BY sale_date) AS prev_sale,
    LEAD(amount, 1, 0) OVER (PARTITION BY department ORDER BY sale_date) AS next_sale,
    amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS growth
FROM sales;

-- Month-over-month growth
SELECT
    sale_date,
    amount,
    amount - LAG(amount) OVER (ORDER BY sale_date) AS mom_change,
    ROUND(100.0*(amount - LAG(amount) OVER (ORDER BY sale_date)) 
        / LAG(amount) OVER (ORDER BY sale_date), 2) AS pct_change
FROM monthly_sales;