Create Country-Level Spend Report Using Pandas

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.

Country-level total and average transaction amounts

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.

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	user_id
US	1
CA	2
US	3

Table transactions

column	type	constraints
txn_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	txn_id	user_id
30.5	10	1
15	11	1
20	12	2

Expected output

One row per country showing total and average transaction amounts for users with at least one transaction, ordered by total_amount in descending order.

average_amount	country	total_amount
22.75	US	45.5
20	CA	20

Hints

1. Use an INNER JOIN on users.user_id = transactions.user_id to keep only matching rows.
2. Group by u.country and compute SUM(t.amount) and AVG(t.amount).
3. ORDER BY total_amount DESC.