CodeRang
SQL topics

SQL LAG: compare a row with the previous row

SQL LAG returns a value from a previous row within a window. Learn how ORDER BY, PARTITION BY, offsets, default values, and the related LEAD function control row-to-row comparisons.

Updated · SQL Arena team

Window functions trackAll problems

What you will practice

  • Read the previous value with LAG
  • Read the next value with LEAD
  • Calculate change from a prior observation
  • Create independent sequences with PARTITION BY
  • Control the boundary value with LAG defaults

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Calculate the change from the previous balance

The member_balances table contains loyalty-point history. For every record, return member_id, balance_date, and points_change, where points_change is the current points minus the member's previous points value. The first record for each member should return 0. Sort by member_id and balance_date.

Query 1PostgreSQLv16
SELECT member_id,
       balance_date,
       points - LAG(points, 1, points) OVER (
         PARTITION BY member_id ORDER BY balance_date
       ) AS points_change
FROM member_balances
ORDER BY member_id, balance_date;
Submit
Resultstore_membersmember_balances
Answer accepted

How SQL LAG works with OVER

LAG(points, 1, points) asks for the points value one row earlier. The first argument is the expression to read, the second is the offset, and the third is a default used when that earlier row does not exist. Because the default is the current points value, the first calculation becomes points minus itself, producing 0.

PARTITION BY member_id is what keeps each member's history separate. Without it, all rows would form one sequence, so the last balance of one member could become the previous value for another. ORDER BY balance_date inside OVER defines the chronological sequence used by the window function. That ordering affects the calculation itself.

The final ORDER BY serves a different purpose: it controls how the completed result is displayed. LEAD uses the same window pattern but looks forward instead. For example, LEAD(balance_date) can expose the next event date, while LAG(balance_date) can measure the time since the previous event. The same pattern appears in growth calculations, sensor readings, purchase histories, and event streams.

SQL LAG and LEAD move in opposite directions within an ordered window. LAG reads from an earlier row, while LEAD reads from a later row. The ORDER BY inside OVER determines what earlier and later mean, so a meaningful and deterministic ordering is important. An optional second argument controls the offset, and a third argument supplies a default when the requested row does not exist. PARTITION BY creates separate sequences, which is useful when changes must be calculated independently for each customer, account, device, or other entity.

SQLPostgreSQL v16
SELECT member_id,
       balance_date,
       points - LAG(points, 1, points) OVER (
         PARTITION BY member_id ORDER BY balance_date
       ) AS points_change
FROM member_balances
ORDER BY member_id, balance_date;
Query result
member_idbalance_datepoints_change
12026-09-010
12026-09-0835
12026-09-15-15
22026-09-020
22026-09-0930
22026-09-1640
32026-09-030
32026-09-1030

Common SQL LAG and LEAD mistakes

01

Leaving out PARTITION BY

When the table contains several independent entities, one global sequence mixes them together. The first row for one member may then compare against the final row of a different member.

02

Using an ambiguous window order

Previous and next only make sense relative to an ordering. If multiple rows tie on the ORDER BY expression and no additional column resolves the tie, the intended sequence may not be fully defined.

03

Replacing every boundary NULL with zero

LAG returns NULL by default when no previous row exists, and sometimes that is exactly the correct meaning. A custom default should represent the business rule, not simply make NULL disappear.

Practice problems on this topic

Window functions track

The useful part of LAG appears when the comparison drives a larger rule. SQL Arena has exercises for days between purchases, differences from previous readings, ratios against a user's prior revenue row, and repeat orders within a time window. Harder tasks use gaps between events to split activity into sessions or detect transitions between periods. Those problems force you to choose the right partition and ordering instead of treating the window syntax as a template. For a structured next step, continue with the “Window Functions from Start to Finish” track. SQL Arena also provides ER diagrams and EXPLAIN, while the AI mentor can provide hints and explain errors within its available free limit.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What does SQL LAG do?

SQL LAG returns an expression from a previous row in the current window. It is commonly used for changes, previous values, and intervals between records.

How do I use LAG in SQL?

Use LAG(expression) with an OVER clause that defines the row order. You can also provide an offset, a default value, and PARTITION BY for independent groups.

What is the difference between SQL LAG and LEAD?

LAG accesses an earlier row and LEAD accesses a later row. Both functions depend on the ORDER BY specified inside their window.

Why use PARTITION BY with SQL LAG?

PARTITION BY separates the data into independent windows. LAG then looks for a previous row only within the same partition.

See also

Practice SQL LAG and LEAD

SQL Arena contains 1,192 automatically checked tasks. Answers are tested on hidden data, so the query has to express the rule rather than match a few visible rows. PostgreSQL, MySQL, ClickHouse, and BigQuery each have a browser-based sandbox, and 292 tasks are available free without a card.

Practice SQL LAG and LEAD

More practice from CodeRang