Create Country-Level Spend Report Using Pandas
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
users
+---------+---------+
| user_id | country |
+---------+---------+
| 1 | US |
| 2 | CA |
| 3 | US |
+---------+---------+
transactions
+--------+---------+--------+
| txn_id | user_id | amount |
+--------+---------+--------+
| 10 | 1 | 30.5 |
| 11 | 1 | 15.0 |
| 12 | 2 | 20.0 |
+--------+---------+--------+
##### Scenario
E-commerce analytics team has separate user and transaction tables and needs country-level spend reporting.
##### Question
Using pandas, merge the users and transactions DataFrames on user_id; keep only rows with matching users. Group the merged result by country, computing total and average amount, and return a DataFrame sorted by total spend descending.
##### Hints
Apply pandas merge, groupby, agg, and sort_values correctly.
Quick Answer: This question evaluates proficiency in data manipulation using pandas, including relational joins and aggregation to produce country-level summary statistics like totals and averages.
