intermediate

LAG Function

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

Overview

LAG() accesses data from a preceding row at a given offset within a partition, without needing a SELF JOIN.

Learning Objectives

  • Retrieve values from prior rows using LAG(column, offset, default).
  • Compute period-over-period differences (e.g. Month-over-Month growth).
  • Handle initial missing values using default fallback parameters.

Detailed Concept Explanation

LAG() looks back $N$ rows behind the current row within the window partition: LAG(column, offset, default_value)

  • column: The column value to retrieve.
  • offset (optional, default 1): How many rows back to look.
  • default_value (optional, default NULL): Value to return if the offset goes beyond the start of the partition.

Code Examples

SQL

sql
-- Calculate Month-over-Month sales difference
SELECT 
    sales_month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY sales_month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY sales_month) AS mom_growth
FROM monthly_sales;

Best Practices

  • Supply Default Parameter: Always provide a default parameter (e.g. LAG(col, 1, 0)) to prevent NULL values on the first row of each partition.

Interview Perspective

Note

Interview Question: How do you calculate Month-over-Month (MoM) revenue growth in SQL?

Use LAG(revenue, 1) OVER (ORDER BY month) to fetch the previous month's revenue, then subtract it from the current month's revenue.


Interactive Challenges

Challenge 1: Fetch Previous Amount

Select 'order_date', 'amount', and LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount from 'orders'.


Summary

LAG() looks back at previous rows in a partition, making period-over-period trend analysis easy.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.