Most active card holder in every country
Nimbus Bank is launching a country-by-country ambassador programme and wants the most active card user in each market. Activity is the number of card payments (kind equal to CARD) made on any of the customer's accounts. Several customers can tie for first place, and the programme wants all of them.
Dataset: Nimbus Bank. transactions.amount_usd is signed (money in is positive, money out is negative), already converted to USD. kind is CARD, SALARY, TRANSFER_IN or TRANSFER_OUT; merchant_id is only filled for card payments. accounts.opening_balance is the balance on 2026-01-01.
Task: for every country return the customer or customers with the most card payments. Columns: country, name, card_payments and card_spend_usd (total spend as a positive number, ROUND(x, 2)). Only customers with at least one card payment compete. Sort by country, then name.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | customer_id INTEGER | account_type TEXT | opened_on TEXT | opening_balance REAL |
|---|---|---|---|---|
| 1 | 1 | CURRENT | 2025-01-23 | 6938.69 |
| 2 | 2 | CURRENT | 2025-07-24 | 1839.27 |
| 3 | 3 | CURRENT | 2025-05-30 | 8009.17 |
| 4 | 4 | CURRENT | 2025-08-26 | 3864.19 |
| 5 | 5 | CURRENT | 2025-10-25 | 1836.52 |
| 6 | 6 | CURRENT | 2025-02-28 | 3834.62 |
Showing the first 6 of 36 rows.
| id INTEGER | name TEXT | country TEXT | segment TEXT | joined_on TEXT |
|---|---|---|---|---|
| 1 | Amara Okafor | United States | Retail | 2025-01-18 |
| 2 | Liam Carter | United Kingdom | Retail | 2025-07-23 |
| 3 | Priya Nair | Germany | Student | 2025-05-30 |
| 4 | Hans Becker | India | Student | 2025-08-23 |
| 5 | Sofia Rossi | Pakistan | Retail | 2025-10-24 |
| 6 | Mateo Alvarez | Brazil | Retail | 2025-02-24 |
Showing the first 6 of 30 rows.
| id INTEGER | customer_id INTEGER | principal_usd REAL | apr_pct REAL | issued_on TEXT | status TEXT |
|---|---|---|---|---|---|
| 1 | 2 | 15150 | 18.98 | 2025-12-14 | CURRENT |
| 2 | 20 | 12450 | 13.29 | 2025-07-02 | CURRENT |
| 3 | 30 | 12950 | 22.93 | 2025-09-12 | CURRENT |
| 4 | 22 | 11850 | 7.01 | 2025-07-24 | DEFAULTED |
| 5 | 17 | 10750 | 22.3 | 2025-01-28 | CURRENT |
| 6 | 27 | 1500 | 20.63 | 2026-02-06 | DEFAULTED |
Showing the first 6 of 14 rows.
| id INTEGER | name TEXT | category TEXT | country TEXT |
|---|---|---|---|
| 1 | Brightleaf Grocers | Groceries | United States |
| 2 | Nordmarkt Fresh | Groceries | Germany |
| 3 | Casa Verde Mercado | Groceries | Mexico |
| 4 | Saffron & Salt | Dining | United Kingdom |
| 5 | Harbor Street Ramen | Dining | Singapore |
| 6 | Tandoor Junction | Dining | India |
Showing the first 6 of 18 rows.
| id INTEGER | account_id INTEGER | merchant_id INTEGER | txn_ts TEXT | kind TEXT | amount_usd REAL | is_flagged INTEGER |
|---|---|---|---|---|---|---|
| 1 | 1 | 14 | 2026-01-03 18:13:47 | CARD | -374.1 | 0 |
| 2 | 12 | 8 | 2026-01-04 22:16:50 | CARD | -853.4 | 0 |
| 3 | 3 | NULL | 2026-01-06 15:14:07 | TRANSFER_IN | 1207.29 | 0 |
| 4 | 5 | 8 | 2026-01-08 15:50:24 | CARD | -820.29 | 0 |
| 5 | 25 | NULL | 2026-01-09 14:38:54 | TRANSFER_OUT | -63.06 | 0 |
| 6 | 30 | 1 | 2026-01-10 16:27:42 | CARD | -72.24 | 0 |
Showing the first 6 of 347 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
RANK() gives tied rows the same rank, ROW_NUMBER() would hide the ties.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.