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.
Overview: This question evaluates proficiency in data manipulation using pandas, including relational joins and aggregation to produce country-level summary statistics like totals and averages.
You are given a users table and a transactions table. Write an SQL query to:
1) Perform an INNER JOIN between users and transactions on user_id (keeping only users that have at least one transaction).
2) Group the joined data by country.
3) For each country, compute the total transaction amount and the average transaction amount.
4) Return the result ordered by total transaction amount in descending order.
Name the output columns country, total_amount, and average_amount.
Tables
users(user_id INTEGER, country VARCHAR(2))
transactions(txn_id INTEGER, user_id INTEGER, amount DECIMAL(10,2))
Hints
- Use an INNER JOIN on users.user_id = transactions.user_id to keep only matching rows.
- Group by u.country and compute SUM(t.amount) and AVG(t.amount).