Schema and sample data
| month | revenue |
|---|---|
| 2024-01 | 100000.00 |
| 2024-02 | 125000.00 |
| 2024-03 | 100000.00 |
| 2024-04 | 150000.00 |
| 2024-05 | 135000.00 |
Show setup SQL (copy to run)
CREATE TABLE MonthlySales (
month CHAR(7) PRIMARY KEY,
revenue DECIMAL(12,2) NOT NULL
);
INSERT INTO MonthlySales (month, revenue) VALUES
('2024-01', 100000.00),
('2024-02', 125000.00),
('2024-03', 100000.00),
('2024-04', 150000.00),
('2024-05', 135000.00);The MonthlySales table holds one row per calendar month, where month is a CHAR(7) string like 2024-01 and revenue is that month's total sales. For every month return four columns: the month, its revenue, the previous month's revenue as prev_revenue, and the month over month percent growth as pct_growth, rounded to two decimals.
Percent growth is (revenue - prev_revenue) / prev_revenue * 100. The earliest month has no prior month, so both prev_revenue and pct_growth must be NULL for that row. Sort the result by month ascending.
Expected result
| month | revenue | prev_revenue | pct_growth |
|---|---|---|---|
| 2024-01 | 100000.00 | NULL | NULL |
| 2024-02 | 125000.00 | 100000.00 | 25.00 |
| 2024-03 | 100000.00 | 125000.00 | -20.00 |
| 2024-04 | 150000.00 | 100000.00 | 50.00 |
| 2024-05 | 135000.00 | 150000.00 | -10.00 |
Show hint
Reach for a window function that can peek at the value from the row before the current one, ordered by month. Once you have the previous revenue, the growth is just arithmetic - but the very first month has no previous value, so protect the division so you never divide by zero.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
CAST(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100
AS DECIMAL(6,2)
) AS pct_growth
FROM MonthlySales
ORDER BY month;LAG(revenue) OVER (ORDER BY month) returns the revenue from the row one position earlier in the ordering. Because the window is ordered by month, each row sees the previous calendar month. For the earliest month (2024-01) there is no prior row, so LAG returns NULL - which is exactly why prev_revenue and pct_growth are both NULL on that first row.
The growth formula is (revenue - prev_revenue) / prev_revenue * 100. The danger is the division: if a prior month had a revenue of 0 you would hit error 8134, divide by zero. Wrapping the denominator in NULLIF(prev_revenue, 0) turns a 0 denominator into NULL, so the whole expression evaluates to NULL instead of raising an error. It also gives the first month a clean NULL result rather than a crash, since NULL arithmetic already propagates NULL.
The CAST(... AS DECIMAL(6,2)) rounds the ratio to two decimals so 25.00 reads as a percentage rather than 25.000000. A common mistake is to compute LAG once in a subquery or CTE and reuse it - that is cleaner, but repeating the identical LAG(revenue) OVER (ORDER BY month) expression, as done here, produces the same result because each occurrence is evaluated against the same window. Another trap is dividing before guarding: always apply NULLIF to the denominator, not the numerator.