SQL LAG and LEAD Functions Explained

Previous and next rows

LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) means: for each order, take the amount of the same customer's previous order by date. The window never crosses into another customer.

A second argument looks further back: LAG(amount, 2) is two orders ago. A third argument sets a default instead of NULL.

Dates between rows

Gaps in days are written differently per database: DATEDIFF(order_date, LAG(order_date) OVER (…)) in MySQL, and plain subtraction order_date - LAG(order_date) OVER (…) in PostgreSQL. The examples below stay with amounts so they run unchanged in both.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT customer_id, order_id, amount,
       LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders
ORDER BY customer_id, order_date;

SELECT customer_id, order_id, amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS amount_change,
       LEAD(order_id) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order
FROM orders
ORDER BY customer_id, order_date;

Common mistakes

• Forgetting PARTITION BY. Without it, the first order of customer 2 is compared with the last order of customer 1.
• Filtering on LAG in WHERE. Window functions are computed after WHERE. Compute LAG in a CTE, then filter in the outer query.
• Ignoring the first row. Arithmetic with NULL gives NULL. Decide whether the first row should be dropped, kept as NULL or defaulted with COALESCE.

Practise LAG and LEAD

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Month-over-Month Revenue Growth

   Compute monthly successful payment volume and its percentage change versus the prior month.

   Medium✓ Solved
2. 2

   Current and Previous Subscription Plan

   From a plan-change history, return each user's current plan and the one before it.

   Medium✓ Solved
3. 3

   Ordered Purchase Funnel

   Measure a three-step conversion funnel where the steps must happen in order.

   Medium✓ Solved
4. 4

   Average Days Between Orders

   Measure each repeat customer's average gap between consecutive orders.

   Medium✓ Solved
5. 5

   Advocate Program Long-Call Growth

   Measure month-over-month growth in the number of long support calls.

   Medium✓ Solved
6. 6

   Drivers With Back-to-Back Drops

   Flag drivers whose weekly completed trips fell by 30% or more two weeks in a row.

   Medium✓ Solved

Want a full set? Work through the Window Functions study plan.

Questions people ask

Are LAG and LEAD available in MySQL?

Yes, since MySQL 8.0, with the same syntax as PostgreSQL.

How do I compare with the same month last year?

Use LAG(value, 12) over months ordered by date, as long as there is exactly one row per month.

Related concepts

• SQL Running Totals and Moving Averages with Window Functions
• SQL Gaps and Islands: Finding Consecutive Streaks
• SQL Date Functions: Grouping, Filtering and Extracting Dates