Mastering Window Functions in SQL: Boost Your Data Analysis Skills

Window functions are powerful SQL features that let you perform calculations across rows related to the current row without collapsing the result set. Unlike aggregate functions that group rows into a single output, window functions preserve individual row details while providing cumulative, ranking, or moving averages insights.

Let's explore the basics of window functions with some practical examples using a sample sales table with columns: sales_id, employee_id, sale_date, and sale_amount.

1. ROW_NUMBER(): Assigns a unique sequential number to rows within a partition, ordered by specified columns.

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

In this example, sales are ranked by date for each employee, with the earliest sale having rank 1.

2. RANK(): Similar to ROW_NUMBER but assigns the same rank to tied values and leaves gaps in ranking.

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

Here, sales are ranked within each employee by sale amount, assigning the same rank to ties.

3. SUM() OVER(): Calculate running totals or cumulative sums without grouping.

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 calculates a running total of sales for each employee ordered by sale_date.

4. AVG() OVER(): Compute moving averages.

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

This calculates a moving average of the current sale amount and two preceding sales for each employee.

Window functions can be combined and customized using PARTITION BY (to group data logically) and ORDER BY (to order rows within the group). Understanding them allows you to write more efficient, readable queries for complex data analysis.

Start incorporating window functions into your SQL toolkit to boost your ability to gain insights from data without complicated self-joins or subqueries.