A Beginner’s Guide to SQL Window Functions with Practical Examples

SQL window functions are powerful tools that allow you to perform calculations across a set of table rows related to the current row without grouping the data. These functions are useful for tasks like running totals, ranking, moving averages, and more. This beginner-friendly guide will introduce you to some common window functions with practical SQL examples.

Unlike aggregate functions that collapse multiple rows into a single result, window functions perform calculations across a 'window' of rows while retaining the individual row details.

Let’s start with a simple example. Suppose we have a sales table with columns: salesperson, sale_date, and amount.

CREATE TABLE sales (
  salesperson VARCHAR(50),
  sale_date DATE,
  amount INT
);

INSERT INTO sales (salesperson, sale_date, amount) VALUES
('Alice', '2024-01-01', 100),
('Alice', '2024-01-02', 150),
('Bob', '2024-01-01', 200),
('Bob', '2024-01-03', 50),
('Alice', '2024-01-03', 200);

### Example 1: Using ROW_NUMBER() to Assign Unique Row Numbers ROW_NUMBER() assigns a unique number to each row within the partition of a result set, ordered by specified columns.

SELECT
  salesperson,
  sale_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_date) AS sale_rank
FROM sales;

This query assigns a "sale_rank" to each sale by the salesperson, ordered by the sale_date.

### Example 2: Calculating Running Total with SUM() OVER() You can compute a cumulative sum of sales amounts for each salesperson ordered by date.

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

The running_total column shows the cumulative sum of sales amounts up to the current sale for each salesperson.

### Example 3: Using RANK() to Show Tied Rankings RANK() works similar to ROW_NUMBER() but assigns the same rank to identical values with gaps in ranking numbers.

SELECT
  salesperson,
  sale_date,
  amount,
  RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS amount_rank
FROM sales;

This query ranks each sale by amount in descending order for each salesperson. Sales with the same amount get the same rank.

### Example 4: Getting Previous Row Value with LAG() LAG() lets you access previous rows without using self-joins.

SELECT
  salesperson,
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sale_date) AS previous_amount
FROM sales;

Here, previous_amount shows the amount from the previous sale of the same salesperson, or 0 if there is no previous sale.

### Wrap-Up Window functions greatly enhance SQL's ability to perform complex calculations while preserving row-level detail. Practice using ROW_NUMBER(), RANK(), SUM() OVER(), and LAG() to better analyze your data.