How healthy is the loan book?
The credit risk committee meets on Monday. Before it does, the head of lending wants a one-glance picture of the loan book: how many loans sit in each status (CURRENT, PAID, LATE, DEFAULTED) and how much money is behind them.
Task: from loans, return status, the number of loans as loans, total_principal (sum of principal_usd) and avg_principal (average, rounded with ROUND(x, 2)). Sort by total_principal from largest to smallest, then by status.
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?
One GROUP BY, three aggregate functions.
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.