Merge and Clean Customer Order Data for Analysis

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.

Customer Spend with Mode-Imputed Country

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.

Table customers

column	type	constraints
id	INTEGER	PRIMARY KEY
name	VARCHAR(100)
country	VARCHAR(50)

Sample data:

country	id	name
US	1	Alice
UK	2	Bob
NULL	3	Charlie

Table orders

column	type	constraints
id	INTEGER	PRIMARY KEY
customer_id	INTEGER	FOREIGN KEY REFERENCES customers(id)
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	id
250.5	1	101
99.99	2	102
NULL	2	103

Expected output

One row per customer with id, name, country after filling missing with the overall mode of non-NULL countries, and total_spent as the sum of order amounts with NULL treated as 0. Sorted by total_spent descending, then by id ascending for deterministic ordering.

country	id	name	total_spent
US	1	Alice	250.5
UK	2	Bob	99.99
UK	3	Charlie	0

Hints

1. Use a LEFT JOIN from customers to orders and COALESCE(amount, 0) before aggregating.
2. 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.