Month-over-Month Revenue
Compute revenue per calendar month (YYYY-MM) from non-cancelled orders (status <> 'cancelled'). Then compare each month with the previous month that appears in the result.
- revenue: monthly total, ROUND(x, 2)
- prev_revenue: the previous listed month's revenue, NULL for the first month
- change: revenue - prev_revenue, ROUND(x, 2), NULL for the first month
Return columns month, revenue, prev_revenue, change. Order by month ascending.
Schema
CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, order_date TEXT NOT NULL, amount REAL NOT NULL, status TEXT NOT NULL );
Sample data
orders
| order_id | order_date | amount | status |
|---|---|---|---|
| 1 | 2026-01-05 | 1200 | delivered |
| 2 | 2026-01-20 | 800 | delivered |
| 3 | 2026-02-02 | 1500 | delivered |
| 4 | 2026-02-15 | 999 | cancelled |
| 5 | 2026-03-01 | 700 | shipped |
| 6 | 2026-03-28 | 650 | delivered |
Hint 1
Aggregate to one row per month in a CTE first.
Hint 2
LAG(col) OVER (ORDER BY month) reads the value from the row before.
Good practice for: Data Analyst, Business Analyst, Product Manager, Data Scientist
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.