SQL Quest › SQL Interview Questions › Joins
Geographic Concentration — Single-State Banks
Concentration risk — banks with most of their branches packed into a single state lack the geographic diversification regulators prefer, and concentrated portfolios are easier targets for localized fraud schemes (regional real-estate fraud, single-employer payroll fraud). Compute, for each bank, the share of its branches that sit in the bank's HQ state (institutions.state). Flag banks where 80%+ of branches are in the HQ state AND they have at least 5 branches. Show name, state, total_branches, hq_branches, hq_share_pct (rounded to 1 decimal). Order by hq_share_pct desc, then total_branches desc. Top 15.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
institutions
| cert | name | city | state | zip | charter_class | established_date | total_assets | total_deposits | total_equity | office_count | bank_class |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 628 | JPMorgan Chase Bank, National Association | Columbus | OH | 43240 | 8 | 01/01/1824 | 3752662000 | 2697842000 | 335936000 | 5335 | N |
| 3510 | Bank of America, National Association | Charlotte | NC | 28202 | 13044 | 10/17/1904 | 2636823000 | 2101968000 | 246250000 | 3840 | N |
| 7213 | Citibank, National Association | Sioux Falls | SD | 57108 | 1461 | 06/16/1812 | 1836436000 | 1465300000 | 176530000 | 958 | N |
branches
| branch_number | cert | branch_name | address | city | state | zip | service_type |
|---|---|---|---|---|---|---|---|
| NULL | 628 | NULL | 118 E Grand River Ave | Fowlerville | MI | 48836 | FULL SERVICE - BRICK AND MORTAR |
| NULL | 628 | NULL | 7 W Main St | Riverhead | NY | 11901 | FULL SERVICE - BRICK AND MORTAR |
| NULL | 628 | NULL | 203 E Berry St | Fort Wayne | IN | 46802 | LIMITED SERVICE - CONSUMER CREDIT |
Expected output: Geographically concentrated banks
Hint
Concepts
SELECT JOIN GROUP BY Aggregation CASE Multi-JOIN
Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises · CASE WHEN practice
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test