SQL LAG and LEAD

LAG reads a value from the previous row and LEAD reads one from the next row, within a window you define. They make "compared with last time" questions easy: month-over-month growth, days since a customer's previous order, or the change in a price.

Syntax

LAG(column, offset, default)  OVER (PARTITION BY ... ORDER BY ...)
LEAD(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

• offset (optional, default 1) is how many rows to look back or ahead.
• default (optional) replaces NULL when there's no such row.

Examples

1. Previous and next order dates

SELECT order_id,
       order_date,
       LAG(order_date)  OVER (ORDER BY order_date) AS previous_order,
       LEAD(order_date) OVER (ORDER BY order_date) AS next_order
FROM orders
ORDER BY order_date
LIMIT 5;

Result:

(5 rows)

The first row has no previous order, so LAG returns NULL.

2. Days between a customer's orders

Add PARTITION BY customer_id so the comparison stays within each customer:

SELECT customer_id,
       order_id,
       order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
FROM orders
WHERE customer_id IN (1, 3)
ORDER BY customer_id, order_date;

Result:

(7 rows)

3. Average repeat-purchase gap

Wrap the previous query in a CTE to summarise it:

WITH gaps AS (
  SELECT customer_id,
         order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
  FROM orders
)
SELECT ROUND(AVG(days_since_prev), 1) AS avg_days_between_orders,
       COUNT(days_since_prev) AS repeat_orders
FROM gaps;

Result:

(1 row)

4. Month-over-month growth

The classic analyst question. Aggregate by month first, then compare each month with the one before:

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date)::date AS month,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
    AND o.order_date < '2025-08-01'
  GROUP BY 1
)
SELECT month,
       ROUND(revenue, 0) AS revenue,
       ROUND(LAG(revenue) OVER (ORDER BY month), 0) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS mom_growth_pct
FROM monthly
ORDER BY month;

Result:

(6 rows)

ShopKart is small, so growth jumps around. On real data this query feeds a growth chart directly.

5. A default value instead of NULL

SELECT order_id,
       order_date,
       LAG(order_id, 1, 0) OVER (ORDER BY order_date) AS previous_order_id
FROM orders
ORDER BY order_date
LIMIT 3;

Result:

(3 rows)

6. FIRST_VALUE: compare with the first row

FIRST_VALUE returns the first value in the window. Each customer's first order date next to every order:

SELECT customer_id,
       order_id,
       order_date,
       FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_date
FROM orders
WHERE customer_id IN (1, 4)
ORDER BY customer_id, order_date;

Result:

(7 rows)

LAST_VALUE needs a frame clause to work as expected; that's covered in the next lesson.

Common mistakes

• Missing PARTITION BY. Without it, customer A's first order is compared with customer B's last order.
• Gaps in the data. LAG looks at the previous row, not the previous month. If a month has no orders, it compares with two months ago. Build a complete month list first (see CROSS JOIN).
• Dividing by a zero previous value. Wrap the denominator in NULLIF(..., 0).

Practice question

For each product, list its order lines in date order with the quantity ordered on the previous line for the same product.

SELECT oi.product_id,
       o.order_date,
       oi.quantity,
       LAG(oi.quantity) OVER (PARTITION BY oi.product_id ORDER BY o.order_date) AS prev_quantity
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE oi.product_id IN (101, 102)
ORDER BY oi.product_id, o.order_date;

Key takeaways

• LAG looks back and LEAD looks ahead within an ordered window.
• PARTITION BY keeps comparisons inside each customer, product or group.
• Month-over-month growth = aggregate by month, then LAG.
• FIRST_VALUE returns the first value in the window.