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 a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

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