Merge and Clean Customer Order Data for Analysis
Company: Boston Consulting Group
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
customers
+----+---------+---------+
| id | name | country |
+----+---------+---------+
| 1 | Alice | US |
| 2 | Bob | UK |
| 3 | Charlie | NULL |
+----+---------+---------+
orders
+-----+-------------+--------+
| id | customer_id | amount |
+-----+-------------+--------+
| 101 | 1 | 250.50 |
| 102 | 2 | 99.99 |
| 103 | 2 | NULL |
+-----+-------------+--------+
##### Scenario
A retail company needs to combine customer and order datasets, clean nulls, and prepare the data for downstream analysis.
##### Question
In Python/pandas, merge the two datasets on customer_id, keep all customers, and add a column total_spent that replaces NULL amounts with 0. Fill missing country values with the mode of existing countries. Return the resulting DataFrame sorted by total_spent descending.
##### Hints
Use merge, fillna, and groupby/transform for the mode.
Overview: This question evaluates proficiency in data manipulation with pandas and SQL concepts, focusing on dataset merging, missing-value handling, and computing derived aggregate columns such as total_spent.
Write a SQL query to join customers and orders, compute each customer's total_spent treating NULL order amounts as 0, fill missing customer country values with the overall mode of existing (non-NULL) countries, and return one row per customer sorted by total_spent in descending order. In case of a tie for the mode, choose the alphabetically first country.
Tables
customers(id INTEGER, name VARCHAR(100), country VARCHAR(50))
orders(id INTEGER, customer_id INTEGER, amount DECIMAL(10,2))
Hints
- Use a LEFT JOIN from customers to orders and COALESCE(amount, 0) before aggregating.
- Compute the mode country in a CTE by counting non-NULL countries and using ROW_NUMBER() to break ties alphabetically, then CROSS JOIN it to fill missing countries.