Mastering SQL Window Functions: A Beginner's Guide to Advanced Data Analysis

SQL window functions are powerful tools that allow you to perform advanced data analysis without having to group or aggregate your data in separate queries. Unlike aggregate functions that reduce rows, window functions operate on a set of rows related to the current row and return a value for each row. This makes them ideal for calculating running totals, rankings, moving averages, and much more.

Let's get started with some basic window functions using a simple example. Suppose we have a sales table with the following columns: sale_id, salesperson, sale_amount, and sale_date.

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  salesperson VARCHAR(50),
  sale_amount DECIMAL(10, 2),
  sale_date DATE
);

INSERT INTO sales VALUES
(1, 'Alice', 500.00, '2024-01-01'),
(2, 'Bob', 300.00, '2024-01-02'),
(3, 'Alice', 700.00, '2024-01-03'),
(4, 'Bob', 200.00, '2024-01-04'),
(5, 'Alice', 400.00, '2024-01-05');

### ROW_NUMBER(): Assigning Row Numbers The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of the result set. For example, to number each sale per salesperson by sale date:

SELECT
  sale_id,
  salesperson,
  sale_amount,
  sale_date,
  ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_date) AS row_num
FROM sales;

### RANK(): Ranking Rows with Ties RANK() assigns a rank to each row within a partition, with gaps in the ranking if there are ties. To rank sales per salesperson by sale_amount in descending order:

SELECT
  sale_id,
  salesperson,
  sale_amount,
  RANK() OVER (PARTITION BY salesperson ORDER BY sale_amount DESC) AS rank
FROM sales;

### SUM() as a Window Function: Running Total You can use SUM() over a window to calculate running totals. For example, the cumulative sales amount per salesperson ordered by sale date:

SELECT
  sale_id,
  salesperson,
  sale_amount,
  SUM(sale_amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS running_total
FROM sales;

### AVG(): Moving Average To calculate the moving average of sales amount over the current and previous sale per salesperson, use the ROWS BETWEEN clause:

SELECT
  sale_id,
  salesperson,
  sale_amount,
  AVG(sale_amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

### PARTING Thoughts Window functions make your SQL queries more powerful and concise. They help analyze data trends, rankings, and distributions without complex joins or subqueries. Start practicing with simple examples and gradually explore more functions like LAG(), LEAD(), FIRST_VALUE(), and NTILE() to unlock even more insights from your data.