✏️ Explanatory Question

LEAD and LAG — compare each row to the previous/next (month-over-month growth)

👁 7 Views
📘 Detailed Answer
🟢 Easy
← Previous RANK vs DENSE_RANK vs ROW_NUMBER — predict the exact output with tied values
💡

Answer with Explanation

100

LEAD and LAG — compare each row to the previous/next (month-over-month growth)

Level: Hard — Essential for trend analysis; "compare a row to its neighbour" is a top analytics interview task.

Scenario: The growth dashboard needs a month-over-month % change column — each month compared to the previous one. Traditionally you'd self-join the table to itself on month = month - 1 (ugly and slow). The interviewer wants the modern window-function way.

Sample Data — monthly_revenue

monthrevenue
2026-011000
2026-021200
2026-031100
2026-041600

What LEAD and LAG Do

  • LAG(col, n): Looks backward — returns the value from n rows before the current row (default n = 1).
  • LEAD(col, n): Looks forward — returns the value from n rows after the current row.
Key point: Both take an optional default value as a third argument, used when there's no neighbour (the first row has no previous, the last has no next). Without it, those cells return NULL.

Solution — Month-over-Month Growth

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                    AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)          AS change_amount,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 2
    )                                                     AS pct_change
FROM monthly_revenue
ORDER BY month;
monthrevenueprev_revenuechange_amountpct_change
2026-011000NULLNULLNULL
2026-0212001000+20020.00
2026-0311001200-100-8.33
2026-0416001100+50045.45

The first row is NULL (no previous month) — correct behaviour.

Handling the NULL & Division-by-Zero Safely

SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue,  -- default 0
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
    ) AS pct_change     -- NULLIF avoids divide-by-zero if prev is 0
FROM monthly_revenue
ORDER BY month;

Bonus — Detecting Value Changes with LAG

-- Flag rows where the status changed from the previous row (audit trails)
SELECT
    log_time,
    status,
    LAG(status) OVER (PARTITION BY server_id ORDER BY log_time) AS prev_status,
    CASE WHEN status <> LAG(status) OVER (PARTITION BY server_id ORDER BY log_time)
         THEN 'CHANGED' ELSE 'same' END AS transition
FROM server_logs;
Why not a self-join? A self-join on "previous month" breaks when months are missing (gaps) and is O(n²) on large data. LAG/LEAD work on row position, are gap-tolerant, and compute in a single ordered pass — far faster and cleaner.

Interviewer follow-up: "Compare each month to the SAME month last year (YoY), not the previous month." → Use LAG(revenue, 12) to look back 12 rows — but only if every month is present. If months can be missing, join on an explicit date key instead, since LAG counts rows, not calendar gaps.
← Previous RANK vs DENSE_RANK vs ROW_NUMBER — predict the exact output with tied values