Calculate Revenue Changes and Rolling Totals

How do you optimize SQL queries for performance?

Demonstrate your approach with a PostgreSQL query over the supplied tables. Return monthly revenue by product category for completed orders, including the previous available month, percentage change, and a three-month rolling revenue total.

Output

1. One row per category and revenue month, with month, category, monthly_revenue, previous_month_revenue, revenue_change_percent, and rolling_three_month_revenue.
2. Include only completed orders from January through April 2025. Sort by category, then month.

Output details

• Treat missing category values as Uncategorized.
• Round revenue_change_percent to two decimal places.
• Return NULL for previous_month_revenue and revenue_change_percent when no previous available month exists.
• Return NULL for revenue_change_percent when previous_month_revenue is zero.
• Calculate the rolling total over the current row and up to the two preceding available category-month rows, ignoring calendar gaps.