Calculating Month Over Month Growth From a CSV in SQL

"Up 89% on last month" is one of the most quoted numbers in business. It is simple to compute and simple to get wrong. The calculation needs three things in order: a complete series of months, the previous month placed on the same row as the current one, and a clear idea of what the percentage is relative to. SQL does the middle step with lag(). This article computes month over month growth for a three-month revenue series, shows what each rate means, and lists the ways the number misleads.

Monthly revenue

From the sales table, by month of sold_on: January orders 1, 2 and 3 total 15.00 + 24.00 + 7.50 = 46.50; February orders 4, 5, 6 and 7 total 12.00 + 28.00 + 150.00 + 12.00 = 202.00; March orders 8, 9 and 10 total 300.00 + 46.00 + 36.00 = 382.00. Sum 630.50. The aggregation is the one from resampling.

The query

WITH m AS (
  SELECT date_trunc('month', sold_on) AS month, sum(units * price) AS revenue
  FROM read_csv('sales.csv') GROUP BY 1
)
SELECT month, round(revenue, 2) AS revenue,
       round(revenue - lag(revenue) OVER (ORDER BY month), 2) AS change,
       round(100 * (revenue - lag(revenue) OVER (ORDER BY month))
                 / lag(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM m ORDER BY month;

lag(revenue) OVER (ORDER BY month) returns the previous row's value. For January there is none, so the change and growth are NULL, which is right: growth from nothing is undefined, and showing 0 or 100% would be a lie. The percentages: 155.50 / 46.50 = 3.344, or +334.4%; 180.00 / 202.00 = 0.891, or +89.1%. A one-liner is revenue / lag(revenue) OVER (ORDER BY month) - 1.

Read both columns

The percentage and the absolute change disagree about what happened. The first month's growth is 334.4%, an enormous figure, and the absolute change, 155.50, is the smaller of the two. The second is 89.1% and 180.00. Growth percentages are dominated by the base: January's 46.50 is small, so any increase looks huge. When the previous month is small, quote the absolute change, or say "from 46.50 to 202.00". The same holds in reverse for a drop from a large base. Report both and the two values behind them, so the reader can check.

It also helps to see what drove the change. February's jump is the 150.00 desk order (order 6) plus 28.00 of pens; March's is the 300.00 desk order (order 8). Take the large orders out and the picture changes: February without order 6 is 52.00 and March without order 8 is 82.00, growth of 11.8% and 57.7% (compared with January and with each other). The growth rate here is a story about two desk sales, which is the kind of caveat that belongs next to the number (finding outliers).

A missing month breaks lag

lag looks at the previous row, not the previous month. If a month has no data it has no row, and the next month's lag returns the month before the gap. Suppose April had no orders and May had 100.00: lag would compare May to March (382.00) and report −73.8% for "month over month", when April was 0 and May's growth from April is undefined. Fix it by building the full list of months first and left joining the totals:

WITH months AS (
  SELECT d::DATE AS month FROM range(DATE '2025-01-01', DATE '2025-06-01', INTERVAL 1 MONTH) t(d)
), m AS (
  SELECT date_trunc('month', sold_on) AS month, sum(units * price) AS revenue FROM read_csv('sales.csv') GROUP BY 1
)
SELECT months.month, coalesce(m.revenue, 0) AS revenue
FROM months LEFT JOIN m USING (month) ORDER BY 1;

Now empty months are rows with 0 (or NULL, if you do not know), and lag compares neighbors correctly. With a zero base, growth is a division by zero: use nullif(lag(...), 0) and show it as "n/a". The gaps article discusses zero versus NULL.

Compare like with like

Months are unequal. A 28-day February and a 31-day March differ by 11%, so a total grows for free. Fixes: compare daily averages (revenue / days_in_month); compare the same number of days (the first 15 days of each month); or compare with the same month last year (year over year), which removes seasonality along with length. Weekday composition matters too: a month with five Saturdays differs from one with four for a business that sells at weekends.

Partial months are the most common error. A report run on 20 March compares 20 days of March with 28 days of February and announces a fall. Either exclude the current month, or compare it with the same days of the previous month ("month to date").

Compound growth and averages

An average of several monthly growth rates is not the growth over the period. If revenue goes up 334.4% and then 89.1%, the average of those, 211.8%, is not an overall rate; the overall change from January to March is (382.00 − 46.50) / 46.50 = 721.5%. For a per-month rate that compounds to the overall change use the geometric mean, (last / first) ^ (1 / months) − 1: (382 / 46.5)^(1/2) − 1 ≈ 186.6% per month, which over two months gives back 8.215 times the start. Say which one you report.

In pandas

m = df.assign(revenue=df.units * df.price).set_index("sold_on").resample("MS")["revenue"].sum()
growth = m.pct_change() * 100          # NaN for the first month

pct_change() is (x − previous) / previous. Because resample creates a row for every month, empty months appear as 0 and pct_change then returns inf for the month after (division by zero), so replace inf with NaN and look at those rows.

Presenting growth

Put the amounts in the table and the percentages beside them, label the base ("vs previous month"), and mark the first period as having no comparison. A chart of the monthly values with the growth as text labels reads better than a chart of the percentages, which are volatile when the base is small. If the audience will quote a single number, give them the one that survives the checks above: the absolute change, or the growth over a longer period such as quarter over quarter, which smooths a one-off order like the 300.00 desk.

A habit worth keeping is to recompute the growth by hand for the latest month before you send the report. It takes a calculator and thirty seconds, and it is the check that catches a wrong sort order, a partial month and a shifted calendar.

How to tell it worked

Recompute one growth rate with a calculator from the two monthly totals. Check that the monthly totals add up to the grand total. Make sure the months are consecutive (count them against the range). Check that the last month is complete. Confirm that January shows no growth rather than zero.

When the base is tiny or incomplete

A growth rate is only meaningful if the base is large enough and complete. From 2 to 3 orders is +50%, and tells you nothing. In those cases show counts and amounts, not percentages, or aggregate to a longer period. Pair every growth figure with its two inputs and the period. For a longer view of a series, use a moving average to smooth noise before comparing, or the cohort tools to separate new from existing customers. The analysis guide lists the checks that apply to every number like this one.

Further reading: DuckDB window functions; pandas pct_change.