ISA-001 — Customer Deduplication
🟢 Beginner
⏱ 15 Minutes
⭐ 10 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
Business Context
A retail organization has discovered duplicate customer records appearing in downstream analytical reports.
These duplicate records are causing:
- Incorrect customer counts
- Inaccurate dashboards
- Misleading business metrics
The analytics team requires a clean customer dataset containing only the most recent version of each customer.
Dataset
customer_records
| customer_id | customer_name | updated_at |
|---|---|---|
| 101 | John | 2025-01-01 |
| 101 | John | 2025-02-01 |
| 102 | Alice | 2025-01-10 |
| 103 | Bob | 2025-01-15 |
| 103 | Bob | 2025-03-01 |
Task
Remove duplicate customer records.
Keep only the latest version of each customer based on the updated_at column.
Expected Output
| customer_id | customer_name | updated_at |
|---|---|---|
| 101 | John | 2025-02-01 |
| 102 | Alice | 2025-01-10 |
| 103 | Bob | 2025-03-01 |
Constraints
- A customer may appear multiple times.
- updated_at determines the latest record.
- Final output should contain exactly one record per customer.
Supported Languages
✅ SQL
✅ PySpark
Hint
Consider ranking records within each customer_id and keeping only the most recent entry.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Optimization Discussion
Notebook Workspace
Cell 1Dataset: customer_records
⏱ 15:00
Loading...
Run Results
✓ Workspace Ready
Rows Returned: --
Execution Time: --
Engine: Coming Soon
Execution Engine Coming Soon