Task
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.
Table Definition
marketplace_purchases
| column | type | description |
|---|
| purchase_id | INT | Unique purchase identifier |
| customer_id | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| purchase_date | DATE | Date of purchase |
| amount | DECIMAL(10,2) | Purchase amount |
| purchase_status | VARCHAR(20) | Purchase state such as completed, refunded, or pending |
Sample Data
| purchase_id | customer_id | customer_name | purchase_date | amount | purchase_status |
|---|
| 1008 | 103 | Priya Shah | 2024-02-20 | 40.00 | completed |
| 1002 | 101 | Ava Chen | 2024-03-12 | 120.00 | completed |
| 1005 | 102 | Marcus Lee | 2024-03-15 | 75.00 | refunded |
| 1011 | 104 | Sofia Gomez | 2024-01-10 | 15.00 | completed |
| 1001 | 101 | Ava Chen | 2024-01-05 | 80.00 | completed |
| 1009 | 103 | Priya Shah | 2024-02-20 | 55.00 | completed |
Expected Output
| customer_id | customer_name | purchase_id | purchase_date | amount |
|---|
| 101 | Ava Chen | 1002 | 2024-03-12 | 120.00 |
| 103 | Priya Shah | 1008 | 2024-02-20 | 40.00 |
