Mastering SQL Window Functions: A Beginner's Step-by-Step Tutorial

SQL window functions are a powerful tool that allow you to perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not cause rows to be grouped into a single output row — they retain the original number of rows while calculating values that consider neighboring rows or partitions.

In this tutorial, we will explore some of the most common window functions, how they work, and practical examples to help you master them. We'll use a simple sales table to demonstrate.

Assume we have a table called 'sales' with these columns: id (sale ID), salesperson, region, and amount (sale amount).

CREATE TABLE sales (
    id INT,
    salesperson VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (id, salesperson, region, amount) VALUES
(1, 'Alice', 'East', 500.00),
(2, 'Bob', 'West', 300.00),
(3, 'Alice', 'East', 700.00),
(4, 'Bob', 'West', 200.00),
(5, 'Charlie', 'East', 400.00);

### 1. Using ROW_NUMBER() to Assign Unique Row Numbers

ROW_NUMBER() assigns a unique sequential integer to rows within the partition of a result set, ordered by the specified column(s). It’s helpful when you want to number each row.

SELECT
  id,
  salesperson,
  amount,
  ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS row_num
FROM sales;

This query numbers each sale for every salesperson starting from 1 for their highest sale.

### 2. Calculating Running Totals with SUM() OVER

You can calculate a running total of sales amounts partitioned by salesperson using the SUM() window function.

SELECT
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY salesperson ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

This query adds up the sale amounts for each salesperson in order of sale id, providing a cumulative total.

### 3. Finding the Average Sale Amount with AVG() OVER

To find the average sale amount per region without grouping and losing row-level detail, you can use AVG() as a window function.

SELECT
  id,
  region,
  amount,
  AVG(amount) OVER (PARTITION BY region) AS avg_region_sale
FROM sales;

Here, each row shows the sale details along with the average sales amount of its region.

### 4. Using LAG() and LEAD() to Access Neighboring Rows

LAG() and LEAD() functions allow you to look at previous or next row values without using self-joins.

SELECT
  id,
  salesperson,
  amount,
  LAG(amount) OVER (PARTITION BY salesperson ORDER BY id) AS previous_sale,
  LEAD(amount) OVER (PARTITION BY salesperson ORDER BY id) AS next_sale
FROM sales;

This query lets you compare each sale amount with the previous and next sales made by the same salesperson.

### Summary

SQL window functions are essential for advanced data analysis without losing the detail of each row. They help calculate rankings, running totals, averages, and compare rows effectively. Practice the examples provided and explore more window functions like RANK(), DENSE_RANK(), and NTILE() to expand your SQL skills.