Re-activate Lapsed Customers | Dataford Interview Questions

How would you identify customers who haven't made a purchase in the last 30 days but were previously active at Repsol?

Assume the analysis date is 2025-02-15 and only completed purchases count as activity. Return customers with at least one completed purchase before the 30-day cutoff.

Output

One row per qualifying customer
Columns: customer_id, customer_name, last_purchase_date, and total_purchases
Sort by last_purchase_date ascending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Repsol customer identifier
customer_name	VARCHAR(100)	Customer name
signup_date	DATE	Date the customer registered

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer associated with the purchase
purchase_date	DATE	Date of the purchase
purchase_status	VARCHAR(20)	Purchase status, such as completed or cancelled
amount_eur	DECIMAL(10,2)	Purchase value in euros

Your question is Re-activate Lapsed Customers. 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