What do the LAG and LEAD window functions do? | Interview Question

Q: What do the LAG and LEAD window functions do?

**Target Audience:** Beginners who want to compare a row with the row before or after it, plus candidates who want the arguments, the engine differences and the follow-ups. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SQL Server facts are cited from Microsoft documentation. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First You keep a notebook with one line per month: the month and how much the shop made. Every month you do the same thing: put your finger on this month line, glance **up one line** to see last month, and write the difference in the margin. Sometimes you gl

The problem

Question presented to candidate: "What do LAG and LEAD do? Say I have monthly revenue and I want each month next to the previous month and the change between them — how would you write it, and what happens on the first row?"

What a strong answer should cover:

LAG(expr) returns expr from the row before the current one in the window order, LEAD(expr) from the row after; both keep every row and replace a self join on "the previous row"
Full form LAG(expr, offset, default): offset is how many rows back (default 1), default is returned when that row does not exist (otherwise NULL, so the first row has no previous value)
They need ORDER BY in OVER to mean anything, and PARTITION BY to stop one entity (a user) reading another entity rows
Typical uses: period-over-period change, time between events per user, detecting state changes; filter on the result in an outer query
Gotchas: integer division in percentage change, the default must match the column type on PostgreSQL, and IGNORE NULLS is not available on PostgreSQL 16 or SQLite (SQL Server 2022 has it)

Code / implementation expected: Yes — previous and next value with the difference, plus a per-user version with PARTITION BY.

Schema used by the examples:

lag
lead

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who want to compare a row with the row before or after it, plus candidates who want the arguments, the engine differences and the follow-ups. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SQL Server facts are cited from Microsoft documentation.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

You keep a notebook with one line per month: the month and how much the shop made. Every month you do the same thing: put your finger on this month line

The solution is hidden so you can try first

8 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLEach month with the previous and next month revenue, the change and the % change

SQLThe offset and default arguments: two rows back, 0 instead of NULL, a label for the last row

SQLPARTITION BY user: days since the same user previous login (SQLite date maths)

PostgreSQL: returning users, away 7 days or more (filter on LAG in an outer query)

SELECT user_id, prev_login, login_date, login_date - prev_login AS days_away
FROM (
  SELECT user_id, login_date::date AS login_date,
         LAG(login_date::date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
  FROM logins
) AS t
WHERE login_date - prev_login >= 7
ORDER BY user_id, login_date;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.