Consecutive Month Purchasers | Dataford Interview Questions

Revature wants to identify customers who remain active across consecutive calendar months. Write a PostgreSQL query that analyzes purchase history and returns every consecutive month pair for each qualifying customer.

Requirements

Collapse multiple purchases by the same customer within a month into one monthly record.
Use a window function to compare each purchase month with the customer's previous purchase month.
Treat consecutive months as calendar months, including transitions such as December to January.
Return the customer, the earlier month, the later month, and the number of purchases made in the later month.
Exclude customers without a qualifying consecutive-month pair and order the output by customer ID and later month.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name

purchases

Column	Type	Description
purchase_idPK	INTEGER	Unique purchase identifier
customer_id	INTEGER	References customers.customer_id
purchased_at	TIMESTAMP	Timestamp when the purchase occurred
amount	NUMERIC(10,2)	Purchase amount

Your question is Consecutive Month Purchasers. 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