Store Revenue Against the Previous Calendar Month
The regional manager reviews each store's month against the calendar month just before it.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #386
Problem statement
The regional manager reviews each store's month against the calendar month just before it. A store that had no sales in that previous month (a renovation shutdown, say) has nothing to compare with — its previous figure is NULL, even if it sold in an earlier month.
For every store and month with sales, return store_id, sales_month (YYYY-MM), revenue, prev_revenue (the store's revenue in the previous calendar month, or NULL) and change_pct = 100 × (revenue − prev_revenue) / prev_revenue rounded to 2 decimal places (NULL when prev_revenue is NULL). Order the rows by store_id, then by sales_month.
Tables
Table: StoreSale
| Column | Type |
|---|---|
| sale_id | int |
| store_id | int |
| sale_date | date |
| amount | int |
Primary key: sale_id.
One row per day's banked takings of a store; amount in rupees, always positive.
Examples
Example 1
StoreSale
| sale_id | store_id | sale_date | amount |
|---|---|---|---|
| 1 | 1 | 2024-11-04 | 182400 |
| 2 | 1 | 2024-11-21 | 96300 |
| 3 | 1 | 2024-12-09 | 301250 |
| 4 | 1 | 2025-01-15 | 250875 |
| 5 | 2 | 2024-11-11 | 120000 |
| 6 | 2 | 2025-01-06 | 141500 |
| 7 | 2 | 2025-02-02 | 99050 |
Output
| store_id | sales_month | revenue | prev_revenue | change_pct |
|---|---|---|---|---|
| 1 | 2024-11 | 278700 | NULL | NULL |
| 1 | 2024-12 | 301250 | 278700 | 8.09 |
| 1 | 2025-01 | 250875 | 301250 | -16.72 |
| 2 | 2024-11 | 120000 | NULL | NULL |
| 2 | 2025-01 | 141500 | NULL | NULL |
| 2 | 2025-02 | 99050 | 141500 | -30 |
How to solve Store Revenue Against the Previous Calendar Month
First collapse the daily takings into one row per store per month. The label DATE_FORMAT(sale_date, '%Y-%m') is what is shown; alongside it the query keeps a month number YEAR * 12 + MONTH, which turns calendar arithmetic into integer arithmetic — the previous calendar month is simply month_no - 1, across year boundaries too (December 2024 is one less than January 2025).
Joining the monthly table to itself on p.month_no = m.month_no - 1 (a LEFT JOIN, so months with no predecessor stay) puts the previous month's revenue next to each month. When that month had no sales the join finds nothing and prev_revenue is NULL; the percentage is then NULL automatically, because arithmetic with NULL is NULL.
LAG() is the tempting shortcut, but it returns the previous row, so after a gap it would compare January with November. The window alternative uses LAG and accepts its value only when the lagged month number is exactly one less. Rounding to two places keeps the ratio the same in every engine. Each version groups the sales once.
Reference solution (MySQL)
WITH monthly AS (
SELECT store_id,
DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
MIN(YEAR(sale_date) * 12 + MONTH(sale_date)) AS month_no,
SUM(amount) AS revenue
FROM StoreSale
GROUP BY store_id, DATE_FORMAT(sale_date, '%Y-%m')
)
SELECT m.store_id, m.sales_month, m.revenue,
p.revenue AS prev_revenue,
ROUND(100 * (m.revenue - p.revenue) / p.revenue, 2) AS change_pct
FROM monthly m
LEFT JOIN monthly p ON p.store_id = m.store_id AND p.month_no = m.month_no - 1
ORDER BY m.store_id, m.sales_monthAnother way
WITH monthly AS (
SELECT store_id, DATE_FORMAT(sale_date, '%Y-%m') AS sales_month, MIN(YEAR(sale_date) * 12 + MONTH(sale_date)) AS month_no, SUM(amount) AS revenue
FROM StoreSale GROUP BY store_id, DATE_FORMAT(sale_date, '%Y-%m')
), lagged AS (
SELECT store_id, sales_month, revenue,
LAG(month_no) OVER (PARTITION BY store_id ORDER BY month_no) AS prev_no,
LAG(revenue) OVER (PARTITION BY store_id ORDER BY month_no) AS prev_rev,
month_no
FROM monthly
)
SELECT store_id, sales_month, revenue,
CASE WHEN prev_no = month_no - 1 THEN prev_rev END AS prev_revenue,
CASE WHEN prev_no = month_no - 1 THEN ROUND(100 * (revenue - prev_rev) / prev_rev, 2) END AS change_pct
FROM lagged
ORDER BY store_id, sales_monthAnother way
SELECT m.store_id, m.sales_month, m.revenue,
(SELECT SUM(x.amount) FROM StoreSale x WHERE x.store_id = m.store_id AND DATE_FORMAT(x.sale_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(m.first_day, INTERVAL 1 MONTH), '%Y-%m')) AS prev_revenue,
ROUND(100 * (m.revenue - (SELECT SUM(x.amount) FROM StoreSale x WHERE x.store_id = m.store_id AND DATE_FORMAT(x.sale_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(m.first_day, INTERVAL 1 MONTH), '%Y-%m')))
/ (SELECT SUM(x.amount) FROM StoreSale x WHERE x.store_id = m.store_id AND DATE_FORMAT(x.sale_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(m.first_day, INTERVAL 1 MONTH), '%Y-%m')), 2) AS change_pct
FROM (SELECT store_id, DATE_FORMAT(sale_date, '%Y-%m') AS sales_month, CONCAT(DATE_FORMAT(sale_date, '%Y-%m'), '-01') AS first_day, SUM(amount) AS revenue
FROM StoreSale GROUP BY store_id, DATE_FORMAT(sale_date, '%Y-%m'), CONCAT(DATE_FORMAT(sale_date, '%Y-%m'), '-01')) m
ORDER BY m.store_id, m.sales_month← Cycle Count Shrinkage Value by Store and Month · Slow-Moving SKUs Still Holding Stock →