Month over month revenue growth

Medium

Question

The orders table stores the time of each order as text like 2026-01-31 23:59:59 and its amount.

For every month that has orders, return month (like 2026-01), revenue (the sum of the amounts) and growth_pct. The growth_pct is the change of the revenue compared with the previous month in the table, as a percentage of the previous month, rounded to 1 decimal. A fall gives a negative number, and no change gives 0.0.

The first month has no previous month, so its growth_pct must be NULL. Sort by month.

The order of the rows matters for this question.

window function
lag
month over month
strftime
percentage
cte

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders3 columns · 10 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL, amount INTEGER NOT NULL);

Example rows of the orders table
id	ordered_at	amount
1	2025-10-05 10:00:00	25000
2	2025-10-31 23:30:00	15000
3	2025-11-10 12:00:00	30000
4	2025-11-30 09:00:00	16000
5	2025-12-01 00:00:00	20000
6	2025-12-24 18:00:00	21400
7	2026-01-31 23:59:59	41400
8	2026-02-02 10:00:00	40000
9	2026-02-28 20:00:00	22100
10	2026-03-15 15:00:00	55000

About this SQL

A window function cannot be used inside GROUP BY, so group by month in a CTE first and apply LAG to that result. In SQLite a division by zero returns NULL, in Postgres and MySQL strict mode it is an error, so use NULLIF(previous, 0) to stay safe everywhere. Whole number / whole number is a whole number, so start with 100.0.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready