Rolling 3-Month Average Revenue
Preview mode. Log in to edit, run, submit, and save progress.
Rolling 3-Month Average Revenue
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 |
Examples
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.