Month-over-Month Revenue Growth: Tracking Category Trends Using LAG()

Business Context

TrendMart is a national retail chain operating 72 stores across five regions and selling products across four major categories — Electronics, Apparel, Groceries, and Home & Kitchen. With $6.8M in average monthly revenue, TrendMart's finance and category management teams run a monthly performance review to assess whether revenue is growing, declining, or plateauing across each category. For the current review covering January through June 2024, the Head of Category Management needs a month-by-month revenue summary per category — with each month showing the current revenue, the prior month's revenue, the absolute change in dollars, and the percentage growth rate compared to the previous month.

The SQL Challenge

The core technique is LAG() window function — which reaches back one row within a partition to retrieve the prior period's value without a self-join. You'll use LAG(total_revenue) OVER (PARTITION BY category ORDER BY month) to pull the previous month's revenue for each category independently. From there, percentage growth is computed as (current - previous) / previous * 100. Supporting skills include DATE_TRUNC('month', sale_date) to bucket daily transactions into calendar months, SUM() aggregation to compute monthly revenue per category, and a CTE to cleanly separate the aggregation step from the window function step — a best practice when combining GROUP BY with OVER().

Your Role and Deliverable

You are the data analyst supporting the Category Management team at TrendMart. Your task is to write a SQL query that returns one row per category per month, showing the month, category, current revenue, prior month revenue, absolute revenue change, and MoM growth percentage — rounded to two decimal places. The output will be used to populate the monthly category performance slide in the executive business review deck.