SQL Window Functions - ROW_NUMBER, RANK, and LAG/LEAD

Need to calculate rankings, running totals, or compare rows with adjacent rows without self-joins or subqueries.

Window function patterns:

-- ROW_NUMBER: unique sequential number per partition
SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- Rank employees within each department by salary

-- Top N per group (common interview question)
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) as rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
-- Top 3 salaries per department

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4 (skips after ties)
-- DENSE_RANK: 1, 2, 2, 3 (no gaps)

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- LAG/LEAD: compare with previous/next row
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) as prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) as change,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY date))
    / LAG(revenue) OVER (ORDER BY date), 1) as pct_change
FROM monthly_revenue;

-- Moving average
SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM daily_metrics;

-- Percentile
SELECT
  name,
  score,
  PERCENT_RANK() OVER (ORDER BY score) as percentile
FROM students;

Window functions perform calculations across sets of rows related to the current row, without collapsing them into groups like GROUP BY does. They're essential for analytics queries.

Writing analytics queries in SQL