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_idbypurchase_datedescending. - If two purchases for the same customer have the same
purchase_date, break ties usingpurchase_iddescending. - Return only customers who have at least two completed purchases.
- Output
customer_id,customer_name,purchase_id,purchase_date, andamount. - Order the final result by
customer_idascending.
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 |
Practicing as: Data Engineer interview at MetaHi, I'll play your Meta interviewer for the Data Engineer role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
