SQL- Window Functions

If you've ever needed to calculate a running total, rank rows within groups, or compare a row to the one before it without collapsing your result set with GROUP BY , window functions are the tool for the job. They're one of the most powerful (and underused) features in SQL, and once they click, you'll reach for them constantly.

What Are Window Functions?

A window function performs a calculation across a set of rows that are related to the current row, called the "window", without reducing the number of rows returned. This is the key difference from aggregate functions like SUM() or AVG() used with GROUP BY, which collapse multiple rows into one.

The basic syntax looks like this:

SELECT
  column1,
  column2,
  SOME_FUNCTION() OVER (
    PARTITION BY column_to_group_by
    ORDER BY column_to_order_by
  ) AS result
FROM table_name;

Three parts matter here:

• The function — what calculation to run (SUM, RANK, LAG, etc.)
• PARTITION BY — splits rows into groups the function operates on independently (optional)
• ORDER BY — defines the order rows are processed in, which matters for ranking and running calculations (optional, but required for many functions)

Categories of Window Functions

1. Aggregate window functions

These are your familiar aggregates (SUM, AVG, COUNT, MIN, MAX) applied over a window instead of collapsing rows.

Example: Running total of sales per day

SELECT
  sale_date,
  daily_sales,
  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Example: Each order's amount vs. that customer's average order

SELECT
  customer_id,
  order_id,
  order_amount,
  AVG(order_amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;

Every row keeps its detail, but now carries context about the group it belongs to — useful for spotting outliers (e.g. an order well above a customer's usual average).

2. Ranking functions

These assign a rank or row number within a partition.

• ROW_NUMBER() — unique sequential number, no ties
• RANK() — same rank for ties, next rank skips (1, 2, 2, 4)
• DENSE_RANK() — same rank for ties, no gap (1, 2, 2, 3)
• NTILE(n) — splits rows into n roughly equal buckets

Example: Top 3 highest-paid employees per department

SELECT *
FROM (
  SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM employees
) ranked
WHERE salary_rank <= 3;

This is one of the most common real-world patterns: get the "top N per group" — impossible to do cleanly with a plain GROUP BY.

3. Value/offset functions

These let you look at other rows relative to the current one — no self-joins required.

• LAG(column, n) — value from n rows before
• LEAD(column, n) — value from n rows after
• FIRST_VALUE() / LAST_VALUE() — first or last value in the window

Example: Month-over-month revenue change

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

This replaces a clunky self-join and is the standard way to build period-over-period comparisons.

Practical Use Cases

• Running totals / cumulative sums — cash flow tracking, YTD metrics
• Top-N per category — top products per region, highest scorers per team
• Deduplication — use ROW_NUMBER() partitioned by a key, then filter WHERE row_num = 1 to keep the first (or latest) record per group
• Period-over-period comparisons — month-over-month, day-over-day trends using LAG/LEAD
• Moving averages — smoothing noisy time series data with a bounded frame
• Percent of total — each row's share of its group's total, using SUM() OVER (PARTITION BY ...) in the denominator

Example: Deduplication — keep only the latest record per customer

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_records
) t
WHERE rn = 1;

Example: Moving average (3-row window)

SELECT
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day
FROM sales;

The ROWS BETWEEN ... AND ... clause defines a custom frame — here, the current row plus the two before it, instead of the whole partition.

A Common Pitfall

Window functions run after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT in the logical query execution order. This means you can't filter on a window function's result directly in the same SELECT's WHERE clause, that's why the top-N and deduplication examples above wrap the query in a subquery (or CTE) and filter in an outer layer.

Why They're Worth Learning

Before window functions, these problems typically required self-joins, correlated subqueries, or client-side post-processing, all slower and harder to read. Window functions let the database engine do this work in one pass, and they read closer to how you'd describe the problem out loud: "rank each employee within their department" or "compare this month to last month."