Task
FinEdge wants to identify its highest-volume users in each country based on transaction activity. Write a PostgreSQL query to return the top 3 users per country by total transaction volume.
Requirements
- Join
users and transactions.
- Calculate each user's total transaction volume per country using the sum of
amount.
- Exclude transactions where
amount is NULL.
- Return the top 3 users within each country using a window function.
- Order the final output by
country, then rank, then user_id.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| user_name | VARCHAR(100) | User display name |
| country | VARCHAR(50) | User country |
| signup_date | DATE | Date the user registered |
transactions
| column | type | description |
|---|
| transaction_id | INT | Primary key for each transaction |
| user_id | INT | References users.user_id |
| amount | DECIMAL(10,2) | Transaction amount |
| transaction_date | DATE | Transaction date |
Sample Data
users
| user_id | user_name | country | signup_date |
|---|
| 4 | Diego | Mexico | 2024-01-20 |
| 1 | Alice | USA | 2024-01-10 |
| 7 | Grace | Canada | 2024-03-01 |
| 2 | Bob | USA | 2024-01-12 |
| 9 | Ivan | USA | 2024-03-15 |
| 5 | Elena | Mexico | 2024-02-01 |
transactions
| transaction_id | user_id | amount | transaction_date |
|---|
| 201 | 2 | 300.00 | 2024-04-01 |
| 203 | 1 | 250.00 | 2024-04-03 |
| 210 | 5 | 400.00 | 2024-04-10 |
| 214 | 7 | 100.00 | 2024-04-14 |
| 216 | 10 | 500.00 | 2024-04-16 |
| 218 | 11 | 50.00 | 2024-04-18 |
Expected Output
| country | user_id | user_name | total_volume | country_rank |
|---|
| Canada | 8 | Hannah | 500.00 | 1 |
| Canada | 6 | Farah | 300.00 | 2 |
| Canada | 7 | Grace | 100.00 | 3 |
| Mexico | 5 | Elena | 400.00 | 1 |
| Mexico | 4 | Diego | 250.00 | 2 |
| Mexico | 11 | Kara | 50.00 | 3 |
| USA | 2 | Bob | 450.00 | 1 |
| USA | 1 | Alice | 350.00 | 2 |
| USA | 9 | Ivan | 300.00 | 3 |
