LAG and LEAD: Removing the Self-Join From Every Growth Report

You are self-joining to compare this month against last. One function does it three times faster in half the code.

The problem

Calculate revenue growth against the previous month. There is no “previous month” column — you have to fetch it yourself.

The pattern

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly_revenue;

LAG looks backwards, LEAD looks forwards. Both take two optional extra arguments:

LAG(col, 2)        -- go back 2 rows
LAG(col, 1, 0)     -- go back 1 row, default to 0

Common mistakes

1. Omitting ORDER BY inside OVER

-- Arbitrary result, different on every run
LAG(revenue) OVER ()

LAG is meaningless without an order. ORDER BY inside OVER is mandatory.

2. Omitting PARTITION BY across groups

-- WRONG: Hanoi's January reads Da Nang's December
LAG(revenue) OVER (ORDER BY month)

-- RIGHT
LAG(revenue) OVER (PARTITION BY city ORDER BY month)

This is the quietest bug here: numbers still appear, they are just wrong.

3. Not handling the first row

The first row has no predecessor, so LAG returns NULL, and dividing by NULL also gives NULL. Logically correct — but if the report needs a zero, use LAG(revenue, 1, 0).

4. Missing months

LAG takes the previous row in the result set, not “the previous calendar month”. If February has no data, March silently compares against January. Build a complete month range with a CROSS JOIN first.

The second use: gaps between purchases

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS days_since_last
FROM orders;

From here you get the average purchase cycle and can spot customers whose gaps are widening — the foundation of every churn model.

Practice

• LeetCode 197 — Rising Temperature
• LeetCode 1907 — Count Salary Categories
• LeetCode 1454 — Active Users
• LeetCode 2701 — Consecutive Transactions with Increasing Amounts
• HackerRank — Interviews