SQL QuestSQL Interview Questions › Joins

Geographic Concentration — Single-State Banks

MediumFreeQuerying BasicsJoinsAggregation & GroupingConditional Logic

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

certnamecitystatezipcharter_classestablished_datetotal_assetstotal_depositstotal_equityoffice_countbank_class
628JPMorgan Chase Bank, National AssociationColumbusOH43240801/01/1824375266200026978420003359360005335N
3510Bank of America, National AssociationCharlotteNC282021304410/17/1904263682300021019680002462500003840N
7213Citibank, National AssociationSioux FallsSD57108146106/16/181218364360001465300000176530000958N

branches

branch_numbercertbranch_nameaddresscitystatezipservice_type
NULL628NULL118 E Grand River AveFowlervilleMI48836FULL SERVICE - BRICK AND MORTAR
NULL628NULL7 W Main StRiverheadNY11901FULL SERVICE - BRICK AND MORTAR
NULL628NULL203 E Berry StFort WayneIN46802LIMITED SERVICE - CONSUMER CREDIT

Expected output: Geographically concentrated banks

Hint

JOIN branches and institutions on cert. Use SUM(CASE WHEN b.state = i.state THEN 1 ELSE 0 END) AS hq_branches and COUNT(*) AS total_branches. Filter on the ratio.

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

JPMorgan

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Related questions

Customers Who Never OrderedMedium · FreeAbove-Average Departments (Derived Table)Medium · FreeLEFT JOIN NULL Semantics: Inactive CustomersMedium · FreeManagement Hierarchy OverviewMedium · FreeCount Direct ReportsMedium · FreeCustomer Recency AnalysisMedium · Free

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