Beginner’s Guide to SQL Window Functions with Practical Examples

SQL window functions allow you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, window functions do not group your results into a single output row, which means you can keep the detailed row data while adding summary information.

In this guide, we will introduce some of the most common window functions like ROW_NUMBER(), RANK(), SUM(), and AVG() with simple examples to help you get started.

Assume we have a sales table with columns: `sales_id`, `employee_id`, `sale_amount`, and `sale_date`.

CREATE TABLE sales (
  sales_id INT,
  employee_id INT,
  sale_amount DECIMAL(10, 2),
  sale_date DATE
);

Let’s look at the ROW_NUMBER() function which assigns a unique sequential number to rows within a partition of a result set.

SELECT 
  employee_id,
  sale_amount,
  ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_rank
FROM sales;

This query numbers each sale for every employee ordered by the sale date. The PARTITION BY clause groups rows by employee, and the ORDER BY clause controls the numbering inside each group.

Another useful function is RANK(), which assigns ranks to rows within a partition. Rows with the same value receive the same rank, leaving gaps in the sequence.

SELECT 
  employee_id,
  sale_amount,
  RANK() OVER (PARTITION BY employee_id ORDER BY sale_amount DESC) AS sale_rank
FROM sales;

Here, sales are ranked within each employee group by sale amount in descending order.

Window functions like SUM() and AVG() can calculate running totals or averages without collapsing rows.

SELECT 
  employee_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

This query computes a running total of sales for each employee ordered by the sale date.

Similarly, you can calculate moving averages or other cumulative metrics easily.

To summarize, SQL window functions help you perform advanced analytics without losing the detail level of your data. Key takeaways for beginners include:

- Use `OVER()` clause to define the window - `PARTITION BY` groups rows for the function - `ORDER BY` defines row sequence inside the window - Common functions: ROW_NUMBER(), RANK(), SUM(), AVG()

With practice, window functions become powerful tools for reporting, ranking, and running calculations on your datasets.