Where does the card money go?
Nimbus Bank's marketing team is choosing partners for a cashback programme. They want to know which merchant categories take the biggest slice of all card spending. Card payments are the rows with kind equal to CARD and they carry a negative amount.
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: return each merchant category, its total card spend as spend_usd (a positive number, ROUND(x, 2)) and its share_pct, the percentage of the spend of all card payments, rounded with ROUND(x, 2). Sort by spend_usd from largest to smallest, then by category.
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?
The denominator is the grand total of card spend. A scalar subquery in the SELECT can compute it once.
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.