Store Revenue Against the Previous Calendar Month

Medium
386.

Store Revenue Against the Previous Calendar Month

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

ColumnType
sale_idint
store_idint
sale_datedate
amountint

Primary key: sale_id.

One row per day's banked takings of a store; amount in rupees, always positive.

Example

The rows must come back in the order shown.

StoreSale

sale_idstore_idsale_dateamount
112024-11-04182400
212024-11-2196300
312024-12-09301250
412025-01-15250875
522024-11-11120000
622025-01-06141500
722025-02-0299050

Output

store_idsales_monthrevenueprev_revenuechange_pct
12024-11278700NULLNULL
12024-123012502787008.09
12025-01250875301250-16.72
22024-11120000NULLNULL
22025-01141500NULLNULL
22025-0299050141500-30
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
sale_idint
store_idint
sale_datedate
amountint

Primary key: sale_id.

One row per day's banked takings of a store; amount in rupees, always positive.

Examples

Example 1

StoreSale

sale_idstore_idsale_dateamount
112024-11-04182400
212024-11-2196300
312024-12-09301250
412025-01-15250875
522024-11-11120000
622025-01-06141500
722025-02-0299050

Output

store_idsales_monthrevenueprev_revenuechange_pct
12024-11278700NULLNULL
12024-123012502787008.09
12025-01250875301250-16.72
22024-11120000NULLNULL
22025-01141500NULLNULL
22025-0299050141500-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_month

Another 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_month

Another 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 →