Mastering Window Functions in SQL: Advanced Techniques for Complex Analytics

Window functions offer a powerful way to perform calculations across sets of rows that are related to the current row without collapsing the result set. This tutorial will introduce you to advanced window function techniques, helping you perform complex analytics with ease.

Let's start with the basics. Unlike aggregate functions, window functions do not combine rows into a single output row. Instead, they allow you to calculate running totals, ranks, moving averages, and other analytical results alongside each row.

Here’s a simple example: calculating a running total of sales per salesperson.

SELECT salesperson_id,
       sales_date,
       amount,
       SUM(amount) OVER (PARTITION BY salesperson_id ORDER BY sales_date) AS running_total
FROM sales_data;

In this query, the SUM() function is used as a window function. The PARTITION BY clause groups the rows by each salesperson, and ORDER BY within the window defines the order to calculate the running total.

Now, let's explore some advanced concepts:

1. **ROW_NUMBER(), RANK(), DENSE_RANK()** – Useful for assigning row numbers or rankings within partitions.

SELECT employee_id,
       department,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

2. **LAG() and LEAD()** – Retrieve data from preceding or following rows, useful for comparisons and trend analysis.

SELECT order_id,
       order_date,
       sales_amount,
       LAG(sales_amount, 1) OVER (ORDER BY order_date) AS previous_sales,
       LEAD(sales_amount, 1) OVER (ORDER BY order_date) AS next_sales
FROM orders;

3. **WINDOW FRAME CLAUSES** – Control the set of rows used for calculations. For example, moving averages can be calculated using ROWS BETWEEN.

SELECT sales_date,
       amount,
       AVG(amount) OVER (ORDER BY sales_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales_data;

This query calculates a 3-day moving average (current day + 2 previous days).

4. **Combining multiple window functions in a single query** for more comprehensive analytics.

SELECT employee_id,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
       AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

This example shows how you can easily get each employee's rank and average salary in their department.

By mastering these window function techniques, you'll be able to perform deep data analysis directly in your SQL queries without complex subqueries or temporary tables. Experiment with these examples using your own data to unlock powerful insights!