Month-over-Month Revenue Growth

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of sales transactions. Each row has a sale date and a revenue amount. Write a SQL query to compute the total revenue for each month, the previous month's revenue (prev_revenue), and the month-over-month growth percentage defined as: mom_growth_pct = ROUND((total_revenue - prev_revenue) / prev_revenue * 100, 2) For the very first month, prev_revenue and mom_growth_pct should be NULL. Return month (as YYYY-MM), total_revenue, prev_revenue, and mom_growth_pct ordered by month. Table: Sales

Column Name	Type	Description
id	INT	Primary key
sale_date	DATE	Date of the sale
revenue	INT	Revenue for this sale

Database Schema (Inferred)

Sales

Column Name	Example Value
id	1
sale_date	2023-01-10
revenue	5000

Example

Sales

id	sale_date	revenue
1	2023-01-10	5000
2	2023-01-25	3000
3	2023-02-05	9000
4	2023-02-20	1000
5	2023-03-15	12000
6	2023-04-10	8000

Output

month	total_revenue	prev_revenue	mom_growth_pct
2023-01	8000	NULL	NULL
2023-02	10000	8000	25
2023-03	12000	10000	20
2023-04	8000	12000	-33.33

Explanation:

First group sales by month, then use LAG() window function to get the previous month's total, then compute the percentage change.

Approach hint

Start with the simplest clear approach, explain the trade-off, then move toward the cleaner answer.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.