SQL Quest › SQL Interview Questions › Joins

Trust Banks vs Others — NPL Profile Comparison

MediumFreeQuerying BasicsJoinsAggregation & GroupingConditional Logic

Pattern-based screening. Do banks with 'Trust' in their name (typical wealth-management institutions) carry different credit-quality patterns than other banks? This is the kind of segmentation question fraud analysts run to find anomalous sub-populations. Compare avg npl_ratio + bank count + total assets between Trust banks (name LIKE '%Trust%') and Other banks, using period_end=20251231. Output two rows: bank_type ('Trust' or 'Other'), bank_count, avg_npl_ratio (rounded to 3 decimals), total_assets_billions (rounded to 1 decimal — total_assets is in thousands, divide by 1000000.0 to get billions). Order by bank_count descending.

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

financials

certnameperiod_endtotal_assetstotal_depositstotal_equitynet_incomenet_loansloan_loss_allowancenpl_ratiotier1_capital_ratioreturn_on_assetsreturn_on_equity
628JPMORGAN CHASE BANK NA2025123137526620002697842000335936000496440001471951000255390000.361849801554203415.290358807000361.344855318880572315.32
3510BANK OF AMERICA NA2025123126368230002101968000246250000303800001166885000131880000.3835676494023299612.4718073797839221.154446206302805112.24
7213CITIBANK NATIONAL ASSN202512311836436000146530000017653000015178000700763000171600000.3025425334724433614.3273921607217640.84582576791651018.67

Expected output: Two-row comparison: Trust vs Other

Hint

CASE WHEN i.name LIKE '%Trust%' THEN 'Trust' ELSE 'Other' END AS bank_type, then GROUP BY that. ⚠️ For total_assets_billions divide by 1000000.0 (with .0), not 1000000 — SQLite integer division will silently drop the fractional part.

Concepts

SELECT JOIN GROUP BY CASE Aggregation GROUP BY + CASE

Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises · CASE WHEN practice

In these company practice sets

Morgan Stanley

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