LAG

LAG is the SQL version of glancing at the row just behind you in line — interviewers love it because it checks whether you understand ordering, partitions, and NULL behavior together.

Question: What is LAG in SQL?

Answer: LAG is a window function that lets you read a value from a previous row in the same result set, without using a self-join. You choose an ordering, optionally split the data into partitions, and SQL gives you the value from N rows earlier. The first row in each partition has no previous row, so it returns NULL unless you provide a default.

Interview-Ready Answer: I use LAG when I need the prior row’s value in the same partition, usually for deltas like day-over-day revenue or status changes. It works by sorting rows with ORDER BY inside OVER, then looking back by an offset, which defaults to 1. One important detail is that the ordering must be deterministic, so if ties are possible I add a tie-breaker column.

What LAG does

LAG(expr, offset, default) returns the value of expr from a row earlier in the same ordered window. Think of a window as a moving view over rows: PARTITION BY splits the data into separate mini-lists, and ORDER BY decides the row sequence inside each list.

1. SQL builds the rows that survive FROM, WHERE, GROUP BY, and HAVING.
2. For each partition, it sorts rows by the OVER (...) ORDER BY list.
3. For the current row, LAG looks back offset rows; the default offset is 1 in most engines.
4. If that earlier row does not exist, SQL returns the provided default, or NULL if no default is given.
5. The current row stays in the result set; window functions add context, unlike GROUP BY, which collapses rows.

When and why to use it

• Compare today with yesterday: revenue, temperature, page views.
• Detect changes: status moved from pending to paid.
• Measure gaps: time between events, version jumps, price changes.
• Build running deltas without a self-join, which is often simpler and easier to read.

How it compares to other options

Performance and edge cases

Most engines must sort each partition by the window order, so the main cost is usually the sort: roughly O(n log n) for the ordered rows, followed by a cheap linear pass. If the data is already stored or indexed in partition/order order, the engine may avoid a full sort or reduce work a lot. On large partitions, the sort can spill to disk when memory is tight, which is why a query over a few million wide rows can suddenly get much slower.

Three important gotchas: first, if the ORDER BY is not unique, results can be nondeterministic because SQL may pick different physical row orders. Second, LAG does not mean “previous non-null” — it means the previous row, even if that value is NULL. Third, the default value only applies when the offset row is missing; it does not replace a real NULL stored in the earlier row.

Real-World Example: In a checkout service, you might use LAG to compare each order total with the previous order for the same customer. That helps with fraud checks, pricing audits, and alerts when a discount engine suddenly changes behavior. A common bug is ordering only by timestamp when two events share the same second; then the previous amount can flip between runs. The symptom is flaky dashboards, confusing support tickets, and logs that show inconsistent day-over-day deltas for the same customer.

Follow-up & Tricky Questions:

• How is LAG different from a self-join? LAG is shorter and usually easier to read because the engine handles the row lookup inside the window. A self-join can still work, but you must manually match each row to its predecessor, which is easier to get wrong and often more verbose.
• What does the offset parameter do? It moves further back in the partition: LAG(x, 2) means two rows earlier instead of one. If that row does not exist, SQL returns the default value or NULL.
• Why is ORDER BY mandatory? Because “previous” only makes sense when rows are in a defined sequence. Without a stable order, the database cannot know which row counts as earlier.
• Can you use LAG after GROUP BY? Yes, but only on the grouped result set, not the original raw rows. That is useful when you want the previous day’s total sales, not the previous individual sale.
• How do you find the change from the previous row? Compute the current value minus LAG(value). This is the classic use case for trends and deltas.
• Does LAG skip NULLs? Not in the core standard behavior. It reads the previous row, even if that row’s value is NULL, so you may need extra logic if you want the previous non-null value.
• Do you need a frame clause for LAG? Usually no. LAG depends on partition and order; the frame is not what makes it work, so beginners should focus on the window order first.
• What happens if the order is not unique? The result can change between runs because the engine is free to break ties however it likes. Add a tie-breaker column such as an ID or timestamp plus sequence.

Common Mistakes:

• Forgetting a deterministic order. Fix: add a tie-breaker like sale_id when timestamps can repeat.
• Assuming LAG skips NULLs. Fix: it does not; if you need the previous non-null value, use engine-specific features or a different query pattern.
• Mixing up LAG and LEAD. Fix: LAG looks backward, LEAD looks forward.
• Using the default value as if it were a replacement for stored NULLs. Fix: the default only fills missing rows, not actual null data.

Memory Hook: Think “LAG = look behind.” If the current row is a car in traffic, LAG tells you what the car just behind you was doing.

Cheat Sheet:

• LAG(expr) returns the previous row’s value in the ordered window.
• PARTITION BY resets the sequence for each group.
• ORDER BY defines what “previous” means.
• Default offset is usually 1; missing rows return NULL unless you supply a default.
• Best use: deltas, trends, and change detection.

Practice Tasks:

• Start with a table of daily sales and compute the day-over-day difference with LAG.
• Add PARTITION BY customer_id and verify the first row in each customer group resets correctly.
• Remove the tie-breaker from ORDER BY and observe why the result becomes unreliable when timestamps repeat.