0Pricing
SQL Interview Prep · Lesson

Period-Over-Period Change

Computing month-over-month growth and day-over-day deltas with LAG.

Period-Over-Period Change is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Guaranteed Analyst Question

If you interview for a data analyst role, expect: "Compute month-over-month growth" or "What's the day-over-day change?" It is nearly unavoidable.

The building block is LAG: grab the prior period's value, then compute an absolute delta or a percentage change. This lesson turns LAG into the period-over-period patterns interviewers grade you on.

Absolute Change With LAG

The simplest version is the raw difference between this period and the last. Pull the previous value with LAG and subtract it inline.

The first ordered row has no predecessor, so its change is NULL. That is correct, not a bug: there is genuinely no prior period to compare against.

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY month;

Percentage Change

Interviewers usually want growth as a percent. The formula is (current - previous) / previous, times 100. Express it directly with LAG.

Watch the order of operations and the parentheses; a misplaced one is a classic mistake under pressure.

SELECT
  month,
  revenue,
  100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) AS pct_growth
FROM monthly_sales
ORDER BY month;

Avoid Integer Division

A trap many candidates fall into: if revenue is an integer, (110 - 100) / 100 evaluates to 0 in databases that do integer division.

Multiply by 100.0 (a float literal) or CAST to a decimal first to force floating-point math. Mentioning this proactively shows attention to detail.

SELECT
  month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / LAG(revenue) OVER (ORDER BY month), 2
  ) AS pct_growth
FROM monthly_sales;

Cleaner: Compute Once in a CTE

Calling LAG twice (numerator and denominator) is repetitive and easy to mistype. A common refactor is to compute the previous value once in a CTE, then do the arithmetic in the outer query.

This reads better in a whiteboard interview and avoids divergence between the two LAG calls.

WITH t AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
)
SELECT
  month,
  revenue,
  ROUND(100.0 * (revenue - prev) / prev, 2) AS pct_growth
FROM t;

Guard Against Divide-by-Zero

If a previous period's value can be 0, the percentage formula divides by zero and errors out. Use NULLIF(prev, 0) so the denominator becomes NULL and the result is NULL rather than an exception.

Defensive division is a small touch interviewers notice on production-minded candidates.

SELECT
  month,
  revenue,
  100.0 * (revenue - prev) / NULLIF(prev, 0) AS pct_growth
FROM (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
) s;

Per-Group Period-Over-Period

Most real questions are scoped: month-over-month growth per product or per region. Add PARTITION BY so each group's series is compared only to itself.

Each group's earliest month resets to NULL, never comparing across product boundaries.

SELECT
  product_id,
  month,
  revenue,
  revenue - LAG(revenue) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS mom_change
FROM product_sales;

Year-Over-Year With Offset

For year-over-year on monthly data where rows are one month apart, you compare to the value 12 rows back. The offset argument does this directly: LAG(revenue, 12).

This assumes one row per month with no gaps. If months can be missing, you would instead join on an explicit date, a subtlety worth stating aloud.

SELECT
  month,
  revenue,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_sales
ORDER BY month;

Pre-Aggregate Before Comparing

Raw event tables are not pre-summed by month. A realistic answer first aggregates to one row per period, then applies LAG on the aggregated result, usually via a CTE.

Trying to LAG over un-aggregated rows compares individual transactions, not monthly totals, a common logical error.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;

Interpreting the First NULL

Interviewers sometimes ask: "Why is your first row's growth NULL, and is that acceptable?" The right answer: there is no prior period, so the change is undefined, and NULL correctly represents that.

If the business wants 0 instead, you would use LAG(revenue, 1, 0) or COALESCE, but only if that matches the intended meaning.

Labeling Growth Direction

Interviewers sometimes extend the question: "Also flag whether each month grew, shrank, or stayed flat." Wrap the LAG comparison in a CASE expression.

Comparing against the previous value gives a clear, human-readable trend column on top of the numeric delta, and it gracefully shows NULL or a default label for the first period that has no comparison.

SELECT
  month,
  revenue,
  CASE
    WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'up'
    WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'down'
    ELSE 'flat'
  END AS trend
FROM monthly_sales
ORDER BY month;

Quick Check

Spot the common mistake in percentage-change queries.

Recap

Period-over-period change is LAG plus arithmetic:

  • Absolute change: value - LAG(value) OVER (ORDER BY period).
  • Percent change: 100.0 * (value - prev) / NULLIF(prev, 0).
  • Force float math, guard divide-by-zero, and pre-aggregate to one row per period.
  • Scope with PARTITION BY; use the offset for year-over-year.

Next: splitting rows into buckets and tiers with NTILE.

Frequently asked questions

Is the “Period-Over-Period Change” lesson free?

Yes — the full text of “Period-Over-Period Change” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Period-Over-Period Change”?

Computing month-over-month growth and day-over-day deltas with LAG. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Period-Over-Period Change” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. LAG and LEAD for Adjacent Rows
  2. Period-Over-Period Change
  3. NTILE for Bucketing
  4. FIRST_VALUE, LAST_VALUE and Frame Edges
← Back to SQL Interview Prep