12.4 LAG()

Level: 12 | Version: v1.1 | Author: Meptrasoft

Overview

LAG() returns a value from a previous row in the window.

It is useful when you want to compare the current row with an earlier row.

LAG() → Look backward

Example uses:

Current Revenue
      ↓
Previous Revenue
      ↓
Compare

LAG() — Look at the Previous Row — Figure 1: LAG() retrieves a value from a previous row based on the window ordering.

Syntax

LAG(column_name, offset, default_value)

Parameter	Meaning
`column_name`	Value to retrieve
`offset`	Rows to look back; default is `1`
`default_value`	Value used when no previous row exists

Practice Table

This topic uses:

CREATE TABLE org_revenue (
    organisation VARCHAR(50),
    year INT,
    revenue NUMERIC(12,2)
);

INSERT INTO org_revenue
(organisation, year, revenue)
VALUES
('ABCD News', 2013, 440000),
('ABCD News', 2014, 480000),
('ABCD News', 2015, 490000),
('ABCD News', 2016, 500000),
('ABCD News', 2017, 520000);

Example: LAG()

SELECT
    organisation,
    year,
    revenue,
    LAG(revenue, 1, 0) OVER (
        PARTITION BY organisation
        ORDER BY year
    ) AS previous_revenue
FROM org_revenue
ORDER BY organisation, year;

Result:

year	revenue	previous_revenue
2013	440000	0
2014	480000	440000
2015	490000	480000
2016	500000	490000
2017	520000	500000

For 2013, there is no previous row, so the specified default value 0 is returned.

How LAG() Works

2013 → no previous row → 0
2014 → previous = 2013
2015 → previous = 2014
2016 → previous = 2015
2017 → previous = 2016

The ORDER BY year determines what “previous” means.

PARTITION BY organisation makes the calculation restart for each organisation.

ORDER BY → Defines the row sequence LAG() → Looks backward in that sequence

Comparing Current and Previous Values

A common use is calculating the change:

SELECT
    year,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY year
    ) AS revenue_change
FROM org_revenue;

For example:

2014 → 480000 - 440000 = 40000
2015 → 490000 - 480000 = 10000

This is why LAG() is useful for year-over-year comparisons.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`LAG()` looks at the next row	It looks at a previous row
The previous row is determined automatically	`ORDER BY` defines the sequence
`LAG()` always returns a value for the first row	The first row has no previous row and returns `NULL` or the specified default
`LAG()` changes table data	It only calculates a value in the query result
`PARTITION BY` is mandatory	It is optional

Placement Quick Points

LAG()
→ PREVIOUS ROW

ORDER BY
→ DEFINE SEQUENCE

OFFSET
→ HOW MANY ROWS BACK

DEFAULT
→ VALUE WHEN NO PREVIOUS ROW

LAG() retrieves a value from a previous row.
ORDER BY determines the row sequence.
The default offset is 1.
A default value can be supplied for the first row.
LAG() is useful for comparing current and previous values.

Interview Questions

What is LAG()?

LAG() is a window function that retrieves a value from a previous row.

What determines the previous row?

The ORDER BY inside the window definition determines the sequence.

What happens on the first row?

If there is no previous row, LAG() returns NULL unless a default value is specified.

What does the offset do?

It specifies how many rows to look backward.

LAG(value, 1) → Previous row
LAG(value, 2) → Two rows back

What is a common use of LAG()?

Comparing the current value with a previous value, such as year-over-year revenue.

Practice & Hands-On Exercises

Using org_revenue:

Display the previous year's revenue:

SELECT organisation, year, revenue,
       LAG(revenue, 1, 0) OVER (PARTITION BY organisation ORDER BY year) AS prev_year_revenue
FROM org_revenue;

Find the revenue change from the previous year:

SELECT year, revenue,
       revenue - LAG(revenue) OVER (ORDER BY year) AS revenue_change
FROM org_revenue;

Use LAG(revenue, 2) to look two years back.
Explain why the first row has no previous value.
Explain the role of ORDER BY.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

CURRENT ROW
     ↓
   LAG()
     ↓
PREVIOUS ROW

LAG() looks backward within the window and retrieves a value from a previous row, making it useful for comparing current and earlier records.

End of LAG()