SQL lag: Window Function Examples

Window functions have revolutionized SQL queries by enabling advanced operations across sets of rows without the need for complex joins or subqueries. Among these functions, LAG stands out as a powerful tool for data analysts and developers who need to compare values in one row with those in preceding rows. This article delves into SQL’s LAG function using real-world examples and application scenarios to help you leverage its full potential.

TL;DR

The SQL LAG function allows you to access data from a preceding row in the same result set without a self-join. It’s widely used for comparisons, such as detecting changes between rows or calculating trends. This article explains the syntax, usage scenarios, and performance considerations of LAG. Armed with this knowledge, you’ll be better prepared to write efficient and readable SQL queries with window functions.

What is the SQL LAG Function?

The SQL LAG function is a window function that provides access to a value at a given physical offset before the current row within a query’s partition. It’s a non-aggregate analytical function, meaning it does not collapse rows, but instead adds new columns with the lagged values.

Basic syntax for the LAG function looks like this:

LAG(column, offset, default_value) OVER (
  PARTITION BY partition_column
  ORDER BY order_column
)

• column: The column from which to retrieve the value.
• offset: How many rows back to access (default is 1).
• default_value: Value to return if there is no row at the specified offset (default is NULL).

Common Use Cases for LAG

• Detecting changes in data over time
• Calculating time differences between events
• Generating period-over-period comparisons
• Identifying trends in financial or sales data

Example 1: Calculating Day-Over-Day Differences

Let’s say we have a table sales_data which logs daily sales figures:

CREATE TABLE sales_data (
  sales_date DATE,
  total_sales INT
);

To compare each day’s sales to the previous day’s, we can use the LAG function as follows:

SELECT
  sales_date,
  total_sales,
  LAG(total_sales, 1, 0) OVER (ORDER BY sales_date) AS previous_day_sales,
  total_sales - LAG(total_sales, 1, 0) OVER (ORDER BY sales_date) AS change_from_yesterday
FROM
  sales_data;

This query adds two columns: one for the previous day’s sales, and one showing the difference. This is extremely helpful for analysts tracking sales performance trends.

Example 2: Detecting Status Changes

Assume a system delivery tracking table called shipment_status:

CREATE TABLE shipment_status (
  shipment_id INT,
  status_date DATE,
  status VARCHAR(50)
);

The LAG function can help identify when the status of a shipment has changed:

SELECT
  shipment_id,
  status_date,
  status,
  LAG(status) OVER (PARTITION BY shipment_id ORDER BY status_date) AS previous_status,
  CASE
    WHEN status != LAG(status) OVER (PARTITION BY shipment_id ORDER BY status_date)
    THEN 'Changed'
    ELSE 'No Change'
  END AS status_change
FROM
  shipment_status;

In this example, LAG compares each status with the one before it, allowing us to easily find where state transitions occur within each shipment.

Example 3: Rolling Averages with LAG

Though rolling averages are often computed using windowed aggregate functions like AVG, LAG can be used for a simplified two- or three-point average:

SELECT
  sales_date,
  total_sales,
  (total_sales + 
   LAG(total_sales) OVER (ORDER BY sales_date) + 
   LAG(total_sales, 2) OVER (ORDER BY sales_date)) / 3.0 AS rolling_avg_3_day
FROM
  sales_data;

This technique can be useful when only a few prior data points are needed and resource constraints make aggregation less desirable.

Performance Considerations

While LAG is efficient compared to alternative methods like self-joins, it does have some overhead — particularly when multiple LAG functions are used with different offsets. Indexing the ORDER BY column can help, but always test performance, especially with large datasets.

Key performance tips:

• Use appropriate indexing on columns used in ORDER BY.
• Avoid unnecessary columns in your SELECT clause.
• Partition wisely — large partitions might slow down query execution.

Combining LAG with Other Window Functions

LAG is often most powerful when used in conjunction with other window functions. For example, ranking, cumulative sums, and analytical aggregates can supplement lagged values for more comprehensive reports.

SELECT
  customer_id,
  purchase_date,
  amount_spent,
  LAG(amount_spent) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS previous_amount,
  SUM(amount_spent) OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_spent
FROM
  customer_purchases;

In this example, we’re not only capturing the previous purchase amount but also calculating how much a customer has spent cumulatively over time. This can be highly valuable for CRM and customer lifecycle analysis.

Handling NULL Values and Default Parameters

A key strength of LAG is its ability to specify default values. This avoids NULLs in the output where a previous row doesn’t exist — such as for the first row in a partition. For instance:

LAG(total_sales, 1, 0) OVER (ORDER BY sales_date)

This ensures a ‘0’ is returned instead of NULL for the first row, which is particularly useful in statistical calculations or dashboards.

Conclusion

The LAG function is a cornerstone of SQL window functions and a critical analytical tool in any data professional’s toolkit. It allows for intuitive comparisons between current and previous rows without convoluted SQL logic, making it ideal for time-series analyses, change detection, and trend reporting.

By leveraging LAG, data analysts can write cleaner, more efficient SQL, accelerating development cycles and improving report quality. Whether you’re building financial dashboards, monitoring operational statuses, or analyzing customer behaviors, LAG empowers you to do more with your data.