You are a business intelligence analyst at an e-commerce company. The finance team wants to track how monthly revenue is trending over time to identify periods of strong growth or decline. Specifically, they want a report that shows each month's total revenue alongside the previous month's revenue, and the percentage change between them — a classic Month-over-Month (MoM) growth analysis. You have a single table: orders , which contains order_id , customer_id , order_date (a DATE ), and amount (a NUMERIC representing the order value in USD). Each row represents one completed order placed by a customer. There are no separate tables needed — all the data required for this analysis lives in the orders table. Aggregate total revenue by calendar month (e.g., 2024-01, 2024-02). For each month, include the previous month's revenue using a window function, aliased as prev_month_revenue . Calculate the month-over-month percentage change , aliased as mom_growth_pct , rounded to 2 decimal places. The formula is: ((current - previous) / previous) * 100 . If there is no previous month (i.e., the first month in the dataset), prev_month_revenue and mom_growth_pct should appear as NULL . Return columns: month , total_revenue , prev_month_revenue , and mom_growth_pct . Sort results chronologically by month in ascending order . Hint: Use a CTE to first aggregate revenue per month using DATE_TRUNC('month', order_date) , then apply the LAG() window function in a second step to look back one row and retrieve the prior month's revenue. A common pitfall is trying to reference a window function alias in the same SELECT clause where it is defined — you must wrap it in a CTE or subquery before you can use it in further calculations. Also, be careful to cast values to NUMERIC before division to avoid integer truncation.

Table Setup (SQL Schema)

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO orders (customer_id, order_date, amount) VALUES
  (1, '2024-01-03', 120.00),
  (2, '2024-01-11', 340.00),
  (3, '2024-01-19', 210.50),
  (4, '2024-01-25', 89.99),
  (5, '2024-02-02', 450.00),
  (1, '2024-02-14', 300.00),
  (6, '2024-02-20', 175.00),
  (2, '2024-02-27', 95.50),
  (3, '2024-03-05', 530.00),
  (7, '2024-03-13', 220.00),
  (4, '2024-03-18', 410.75),
  (5, '2024-03-29', 190.00),
  (8, '2024-03-30', 315.00),
  (6, '2024-04-04', 640.00),
  (1, '2024-04-10', 280.00),
  (9, '2024-04-17', 155.00),
  (2, '2024-04-23', 490.50),
  (7, '2024-05-01', 370.00),
  (3, '2024-05-09', 610.00),
  (10, '2024-05-15', 225.00),
  (8, '2024-05-22', 180.00),
  (4, '2024-05-30', 430.00),
  (9, '2024-06-06', 520.00),
  (5, '2024-06-14', 345.00),
  (10, '2024-06-21', 275.00),
  (1, '2024-06-28', 395.00);