Rolling 3-Month Average Revenue

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

Medium

Description

You are given a MonthlyRevenue table with one row per month. Write a SQL query to compute for each month: the revenue for that month and the rolling 3-month average (the average of the current month and the two preceding months). For the first month, the average is just that month. For the second month, the average is over 2 months. Round the rolling average to 2 decimal places. Return the result ordered by month. Table: MonthlyRevenue

Column Name	Type	Description
month	VARCHAR	Month in YYYY-MM format
revenue	DECIMAL	Total revenue for that month

Database Schema (Inferred)

MonthlyRevenue

Column Name	Example Value
month	2023-01
revenue	1000

Example

MonthlyRevenue

month	revenue
2023-01	1000
2023-02	1200
2023-03	900
2023-04	1500
2023-05	1100
2023-06	1300

Output

month	revenue	rolling_3mo_avg
2023-01	1000	1000
2023-02	1200	1100
2023-03	900	1033.33
2023-04	1500	1200
2023-05	1100	1166.67
2023-06	1300	1300

Explanation:

Jan: only 1 row → avg 1000. Feb: (1000+1200)/2 = 1100. Mar: (1000+1200+900)/3 = 1033.33. Apr: (1200+900+1500)/3 = 1200. Each subsequent month uses the current + 2 preceding.

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.