Second Most Recent Marketplace Purchase | Dataford Interview Questions

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Your question is Second Most Recent Marketplace Purchase. Start with the requirements and the one table 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

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Your question is Second Most Recent Marketplace Purchase. Start with the requirements and the one table 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.