Window Functions for Advanced Data Analysis

1. The Core Idea: The OVER() Clause and PARTITION BY

At its heart, a window function performs a calculation across a set of rows that are related to the current row. This "set of rows" is called a window. The OVER() clause is what defines this window, and it's what transforms a regular function (like an aggregate function) into a window function.

The most crucial part of the OVER() clause for our purposes today is PARTITION BY. This sub-clause divides the rows of your result set into smaller, independent groups, or partitions. The window function then operates on each partition separately.

A key distinction to remember is how this differs from GROUP BY:

• GROUP BY collapses multiple rows into a single summary row.
• A window function with PARTITION BY returns a value for every single row, calculated based on the partition that row belongs to.

This video provides an excellent introduction, showing how a familiar aggregate function like MAX() can be used as a window function.

As you saw, the general syntax looks like this:

SELECT
  column_1,
  window_function() OVER (
    PARTITION BY column_to_partition_by
    ORDER BY column_to_order_by
  ) AS new_column_name
FROM your_table;

Window functions can be broadly categorized. This article provides a concise overview.

2. Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK

A common data analysis task is to rank items within categories—for example, ranking the top-selling products in each region or finding the top 3 employees by salary in each department. Ranking functions are designed for exactly this.

The three main ranking functions are:

• ROW_NUMBER(): Assigns a unique, sequential integer to each row within a partition. No two rows will have the same number.
• RANK(): Assigns a rank to each row within a partition. If there are ties in the ordering value, tied rows receive the same rank, and the next rank is skipped. (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Similar to RANK(), but if there are ties, the next rank is not skipped. (e.g., 1, 2, 2, 3).

The following video explains this crucial difference and shows a practical example.

Notice that for ranking functions, the ORDER BY clause inside OVER() is mandatory. It tells the function how to determine the rank.

3. Value Functions: LAG and LEAD

Value functions, also called analytical functions, allow you to access data from other rows within your window. The two most common are LAG and LEAD.

• LAG(): Accesses data from a previous row in the partition.
• LEAD(): Accesses data from a following row in the partition.

These are incredibly useful for calculating period-over-period changes, such as month-over-month growth in sales or the time difference between consecutive events.

A common and powerful use case in data science is time-series analysis. The following video demonstrates how to use LAG to calculate month-over-month performance.

Like ranking functions, LAG and LEAD require an ORDER BY clause within OVER() to establish a clear sequence of rows.

You can also provide optional arguments to LAG and LEAD:

1. Offset: The number of rows to look back or forward (default is 1).
2. Default Value: A value to return if the offset goes beyond the partition's boundary (default is NULL).

The syntax is LAG(column_name, offset, default_value). For example, LAG(sales, 1, 0) would get the sales from the previous row, and if no previous row exists, it would return 0 instead of NULL.

4. Practice Exercises

Now it's your turn to apply these concepts. Below are two problems. Try to write the query to produce the "Desired Output" from the "Input" table. You can find the solutions in the collapsed sections below each problem.

The exercises use the following demand table:

Problem 1: Find the two worst-performing days for each product

Task: Get the days corresponding to the two lowest qty values for each product.

Desired Output:

WITH RankedSales AS (
  SELECT
    product,
    day,
    qty,
    ROW_NUMBER() OVER (PARTITION BY product ORDER BY qty ASC) as rn
  FROM demand
)
SELECT
  product,
  day,
  qty,
  rn
FROM RankedSales
WHERE rn <= 2;

Problem 2: Calculate the percentage change in quantity from the previous day

Task: For each product, calculate the percentage increase or decrease in qty compared to the previous day's qty. Order the results by day. The formula for percentage change is: $\frac{(\text{current}-\text{previous})}{\text{previous}}\times 100$.

Desired Output:

WITH LaggedSales AS (
  SELECT
    product,
    day,
    qty,
    LAG(qty, 1, NULL) OVER (PARTITION BY product ORDER BY day) AS prev_day_qty
  FROM demand
)
SELECT
  product,
  day,
  qty,
  -- Ensure previous quantity is not zero to avoid division by zero error
  CASE
    WHEN prev_day_qty IS NOT NULL AND prev_day_qty != 0 THEN
      ROUND(((qty - prev_day_qty)::numeric / prev_day_qty) * 100, 2)
    ELSE NULL
  END AS perc_increase
FROM LaggedSales
ORDER BY product, day;

Conclusion

In this lesson, you've unlocked one of the most powerful features in SQL for data analysis. Window functions allow you to perform complex calculations like ranking and time-series analysis without resorting to complicated self-joins or slow correlated subqueries.

Key Takeaways:

• Window functions operate on a "window" of rows related to the current row, defined by the OVER() clause.
• PARTITION BY is the key to applying these functions to independent subgroups of your data, such as by department, product_category, or customer_id.
• Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK) are essential for ordering and filtering items within each partition.
• Value Functions (LAG, LEAD) are your go-to tools for comparing a row with its neighbors, forming the basis of many time-series calculations.

In our next lesson, we will delve deeper into customizing the window itself by using frame clauses. You'll learn how to compute running totals and moving averages, which are fundamental techniques in financial analysis, inventory management, and trend analysis.