Articles
Month-over-Month Growth in SQL: Missing Months, Zero Baselines, and Partial Periods
Write month-over-month SQL that handles missing months, zero baselines, partial periods, entity partitions, and comparable reporting windows.
A query reports 50% growth from January to March. February is absent from the sales table, so LAG quietly compares March with January. The arithmetic is correct; the month-over-month claim is wrong. This is the central interview trap: a previous row is not necessarily the previous calendar month.
Reliable month-over-month SQL starts with a comparable monthly series, then calculates absolute and percentage change. This guide works through missing months, zero baselines, partial periods, and reporting filters using an original fixture. Official PostgreSQL documentation supports the function semantics. The metric policies and interview recommendations are editorial reasoning, not candidate reports or promises about a company's interview.
For a related practice prompt, use Write monthly customer and sales SQL queries and explain the calendar assumptions before writing the window function.
Define the metric before the denominator
For a current value C and previous-month value P, absolute change is C - P. Percentage growth is 100 × (C - P) / P when the baseline supports that interpretation. Returning both helps readers distinguish a large percentage on a tiny base from a large absolute change.
Our example measures nonnegative gross sales in one currency. Each order contributes once, timestamps use UTC, and completed months are assumed fully ingested. A month with no qualifying orders therefore has zero sales. We exclude the current incomplete calendar month. Returns, exchange rates, and accounting restatements are outside this fixture's input contract.
These assumptions are not optional decoration. If February's pipeline failed, missing order rows do not establish zero sales. If an entity launched in March, filling January and February with zero may imply a history that did not exist. If you mix currencies, a single sum may lack a meaningful unit.
State the output grain explicitly: one row per calendar month for the whole business in the first query. An entity-specific version needs one row per entity and month, with the window partitioned by that entity.
A sparse fixture with a misleading easy answer
Take a fixed reporting cutoff of May 15, 2026. The requested completed-month report covers January through April. December is loaded as the baseline for January.
| Month | Observed gross sales | Data interpretation |
|---|---|---|
| December 2025 | 100 | Complete baseline |
| January 2026 | 100 | Complete |
| February 2026 | No order rows | Confirmed complete; true zero |
| March 2026 | 150 | Complete |
| April 2026 | 180 | Complete |
| May 2026 | 90 so far | Partial; exclude from this report |
After densifying the calendar, January's growth is 0%, February's is −100%, March's percentage is undefined because its baseline is zero, and April's is 20%. March still has a useful absolute increase of 150.
If you run LAG directly over only months containing orders, March follows January and appears to grow 50%. If you include May's incomplete 90 against April's full 180, the report appears to show a 50% decline. These are different errors, and neither is repaired merely by guarding division by zero.
Official semantics: PostgreSQL defines LAG in terms of an offset in ordered rows within a partition. It does not infer missing calendar periods. See window functions.
Build a calendar, aggregate facts, then compare
The following query uses explicit fixture boundaries rather than the wall clock, which makes the result reproducible. orders contains unique order IDs, a UTC order_time, and non-NULL decimal amount values. The calendar includes December because January needs a baseline.
WITH months AS (
SELECT CAST(month_start AS DATE) AS month_start
FROM generate_series(
TIMESTAMP '2025-12-01 00:00:00',
TIMESTAMP '2026-04-01 00:00:00',
INTERVAL '1 month'
) AS g(month_start)
), monthly_sales AS (
SELECT CAST(date_trunc('month', order_time) AS DATE)
AS month_start,
SUM(amount) AS sales
FROM orders
WHERE order_time >= TIMESTAMP '2025-12-01 00:00:00'
AND order_time < TIMESTAMP '2026-05-01 00:00:00'
GROUP BY 1
), dense AS (
SELECT m.month_start, COALESCE(s.sales, 0) AS sales
FROM months AS m
LEFT JOIN monthly_sales AS s
ON s.month_start = m.month_start
), compared AS (
SELECT month_start, sales,
LAG(sales) OVER (ORDER BY month_start) AS previous_sales
FROM dense
)
SELECT month_start, sales, previous_sales,
sales - previous_sales AS absolute_change,
CASE
WHEN previous_sales > 0
THEN 100.0 * (sales - previous_sales) / previous_sales
END AS growth_pct,
CASE
WHEN previous_sales IS NULL THEN 'no_baseline'
WHEN previous_sales = 0 THEN 'zero_baseline'
ELSE 'comparable'
END AS comparison_status
FROM compared
WHERE month_start >= DATE '2026-01-01'
ORDER BY month_start;
Official building blocks: PostgreSQL documents timestamp series generation and month truncation in its set-returning functions and date/time functions references. The query's zero-fill policy comes from our complete-ingestion assumption, not from those functions.
Notice the order of operations. We aggregate to one row per month before joining the calendar. We calculate LAG before applying the display-range filter. We use a half-open order-time range so an order exactly at May 1 belongs to the excluded period.
The complete query was executed in DuckDB 1.5.2 against the fixture. Its syntax uses PostgreSQL-style functions supported by that runtime; this is not a claim of a PostgreSQL execution benchmark.
Read the expected output as a business statement
| Month | Sales | Previous sales | Absolute change | Growth | Status |
|---|---|---|---|---|---|
| January | 100 | 100 | 0 | 0% | Comparable |
| February | 0 | 100 | −100 | −100% | Comparable |
| March | 150 | 0 | 150 | NULL | Zero baseline |
| April | 180 | 150 | 30 | 20% | Comparable |
Say “March increased by 150 from a zero-sales month; percentage growth is not defined under this metric.” Do not call that 100% growth, and do not convert the NULL percentage into 0%, which would imply no change.
January retains its December baseline even though December is hidden from the final report. Filtering December out before the window calculation would make January look like the first available observation. The extra baseline month is part of the calculation input, not an unwanted output row.
If the reporting range starts later, load the needed prior month as well. Avoid fetching only the visible range and then treating the missing baseline as a genuine lack of history.
Zero, missing, and incomplete are separate states
A robust report needs enough metadata to distinguish “there were no sales” from “we do not know the sales.” A calendar row supplies a place to put a value; it does not prove that the value is known.
For a pipeline with a completeness table, join completeness status at the same entity–month grain. Populate zero only when the month is complete and no qualifying facts exist. Keep an unknown value for incomplete ingestion. Derive growth only when both current and previous months are comparable.
Do not use an option that skips NULL observations to jump over an unknown month. That changes a calendar comparison into a comparison against the last available observation. The distinction is meaningful even if the resulting chart looks smoother.
Similarly, business inactivity differs from data absence. An operating store with no transactions can have zero revenue. A store that has not opened may be outside the eligible population. A permanently closed store might remain in a historical cohort analysis but disappear from an active-store report. Define the population instead of letting table sparsity make the decision.
Handle denominator policies explicitly
A concise formula often uses NULLIF(previous_sales, 0) in the denominator. Official semantics: PostgreSQL returns NULL from NULLIF(a, b) when the arguments are equal. This prevents a zero denominator from being treated as an ordinary nonzero baseline. See conditional expressions.
Our query uses a CASE expression because the metric contract permits ordinary growth only for a positive baseline. This also makes it easier to attach a readable status. A NULL numeric result alone cannot distinguish zero baseline, absent baseline, incomplete data, or an excluded comparison.
If the metric can be negative, such as net revenue after refunds or operating profit, revisit the policy. Moving from −100 to −50 is an improvement in amount, but dividing the change by −100 produces −50%. The conventional signed-baseline formula may mislead the intended reader. Report absolute change and define a separate interpretation rather than silently switching to an absolute denominator.
For both-zero months, the fraction is still undefined mathematically. A dashboard may choose a special “unchanged at zero” status. That is a display policy, not evidence that division by zero equals zero. Keep any chosen presentation rule separate from the underlying numeric calculation.
Compare partial periods only under a stated rule
The completed-month report excludes May. If a stakeholder requests month-to-date comparison, build a different metric with matched cutoffs. For example, compare May 1 through the end of May 14 with April 1 through the end of April 14, provided both intervals are fully loaded in the same reporting timezone.
“Same elapsed days” still does not guarantee identical opportunity. Weekday mix, holidays, launch dates, and business schedules may differ. Mention these limits when interpreting the result; do not automatically annualize or project a partial month as though the observed pace were guaranteed to continue.
Late data creates another boundary. A month can be closed on the calendar but not operationally complete. A report cutoff should reflect both period end and the ingestion or accounting policy. Store the report's as-of time so later reruns can explain changed values.
For local reporting, convert timestamps to the intended business timezone before assigning months, or derive equivalent UTC interval boundaries. An event near midnight UTC can belong to the previous local date and month. Do not mix local grouping with UTC filtering unless the boundaries have been aligned deliberately.
Extend to entities without introducing fan-out
For product, restaurant, or country reports, construct eligible entity–month pairs and aggregate facts to the same grain. Then use LAG(sales) OVER (PARTITION BY entity_id ORDER BY month_start). Omitting the partition lets one entity borrow another entity's last row as its baseline.
The entity list should come from the requested population, not just from customers or stores that happened to transact in the report period. Otherwise, an entity with no current activity may disappear instead of showing a decline.
Be careful when joining orders to order items or category mappings before aggregation. A one-to-many join can repeat an order amount. A perfect date spine and window expression cannot rescue an inflated input measure. Verify the fact grain and relationship cardinality first.
The general date-spine problem is broader than this article's monthly growth calculation. Here the calendar's purpose is specific: ensure that adjacent rows represent adjacent comparable months and preserve the required baseline.
Test the result, not just whether SQL runs
The saved fixture includes two January orders summing to 100, no February orders, and a May order exactly at the exclusion boundary. Verification checks the four output months, their amounts and statuses, the 20% April result, and the retained December baseline.
Useful changes include adding a March order and checking both March's amount and April's denominator, moving an order across a month boundary, and marking February incomplete. For the last change, the zero-fill assumption must be removed or guarded; the original query is not appropriate for unknown ingestion state.
Check a small baseline such as 3 followed by 4 to expose integer-division mistakes in engines where integer arithmetic truncates. Round only for final presentation so downstream calculations retain useful precision. Always label whether a returned value is a percentage, fraction, or percentage-point change.
Practice monthly comparisons with clear explanations
| Question | What to focus on |
|---|---|
| Write monthly customer and sales SQL queries | Monthly grain, prior-period scope, and entity partitions |
| Measure Pirate-Theme Usage and Revenue Growth Over Time | State which growth metric is being measured |
| Write SQL revenue and anomaly queries | Separate data problems from real metric movement |
| Write SQL window functions for D7 retention | Define comparable time windows and populations |
| Solve SQL and Python coding tasks | Validate SQL against a small independent calculation |
Start with Write monthly customer and sales SQL queries. Before optimizing, explain why March's absolute change is meaningful while its percentage growth remains NULL.