First-Order Item Then Churn | Dataford Interview Questions

Write a SQL query to identify customers who purchased a specific menu item in their first order but have not returned to make a purchase in the last 30 days.

For this exercise, use Big Mac as the specific menu item and evaluate recency relative to CURRENT_DATE.

Output

Return one row per qualifying customer.
Include customer_id, customer_name, first_order_date, and last_order_date.
Exclude customers with a purchase on or after the date 30 days before CURRENT_DATE.
Order by customer_id ascending.

Schema

customers

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-line identifier
order_id	INT	Related order
menu_item_id	INT	Purchased menu item
quantity	INT	Quantity purchased

menu_items

Column	Type	Description
menu_item_idPK	INT	Unique menu item identifier
menu_item_name	VARCHAR(100)	Menu item name

Your question is First-Order Item Then Churn. Start with the requirements and the four 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