SQL: Returning Customers Streak | Dataford Interview Questions

Your question is SQL: Returning Customers Streak. Start with the requirements and the two tables on the right.

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

Problem

Movate's customer engagement analytics team wants to identify returning customers who maintained purchasing activity across consecutive calendar months. Write a PostgreSQL query using the customers and purchases tables.

Requirements

Treat multiple purchases by the same customer in one month as a single active month.
Identify every customer streak containing at least three consecutive calendar months.
Do not use LEAD() or LAG().
Return the customer ID, customer name, streak start month, streak end month, and number of consecutive months.
Exclude purchases that do not match a customer and sort by customer ID, then streak start month.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment	VARCHAR(30)	Customer business segment

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer associated with the purchase
purchase_date	DATE	Date on which the purchase occurred
purchase_amount	NUMERIC(10,2)	Purchase amount
channel	VARCHAR(30)	Purchase channel