SQL for Identifying Churned Customers | Dataford Interview Questions

Write a SQL query to identify customers who have churned after their third box.

Use the customers and boxes tables. Treat a customer as qualifying when their third box was delivered before their recorded churn timestamp and their subscription status is churned.

Output

One row per qualifying customer with customer_id, customer_name, third_box_date, and churned_at
Sort by churned_at ascending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name
subscription_status	VARCHAR(20)	Current subscription status
churned_at	TIMESTAMP	Timestamp when the customer churned

boxes

Column	Type	Description
box_idPK	INT	Unique box record identifier
customer_id	INT	Customer who received or was assigned the box
box_number	INT	Sequential box number in the subscription
delivered_at	DATE	Date the box was delivered
delivery_status	VARCHAR(20)	Delivery state of the box

Your question is SQL for Identifying Churned 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