Compare Current vs Previous Month | Dataford Interview Questions

Business Context

Avenue Code Commerce needs a regional performance report showing whether the current month is improving over the previous month.

Task

Write a PostgreSQL query that compares June 2025 metrics with May 2025 for each region. Include only completed orders placed by active customers.

Requirements

Calculate monthly order count and revenue by customer region.
Use a window function to retrieve the previous month's metrics for each region.
Return June's metrics, May's metrics, absolute revenue change, and percentage revenue change. Return NULL for the percentage change when the previous revenue is zero.
Sort the final results alphabetically by region.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
region	VARCHAR(30)	Customer sales region
email	VARCHAR(255)	Customer email address
is_active	BOOLEAN	Whether the customer is active

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status
order_amount	NUMERIC(12,2)	Revenue attributed to the order

Your question is Compare Current vs Previous Month. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem