Customers in Consecutive Months | Dataford Interview Questions

Amazon DSP wants to identify advertisers whose purchasing activity shows consistent month-over-month engagement. Write a PostgreSQL query to find customers with purchases in at least one pair of consecutive calendar months.

Requirements

Treat multiple purchases by the same customer in the same calendar month as one active month.
Identify consecutive months across year boundaries, such as December 2023 and January 2024.
Return each qualifying customer, the number of consecutive-month pairs, and the first month in a qualifying pair.
Exclude rows with a NULL purchase date and order results by pair count descending, then customer ID ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer or advertiser name
region	VARCHAR(30)	Customer's marketplace region

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer who made the purchase
purchase_date	DATE	Date of the Amazon DSP purchase
amount	NUMERIC(10,2)	Purchase amount in USD

Your question is Customers in Consecutive Months. 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