SQL Quest › SQL Interview Questions › Subqueries & CTEs

Above-Median NPL Banks

MediumFreeQuerying BasicsSubqueries & CTEsJoins

Identify banks with elevated non-performing loans (NPL ratio) — defined here as banks whose npl_ratio (period_end=20251231) is above the median NPL ratio across all top-200 banks. NPL ratio is a key credit-quality signal regulators watch. Use a CTE to compute the median (approximated via OFFSET on a sorted list — SQLite has no PERCENTILE_CONT). Show name, state, npl_ratio ordered by npl_ratio descending, 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

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: Banks with above-median credit stress

Hint

WITH ordered AS (SELECT npl_ratio FROM financials WHERE period_end=20251231 ORDER BY npl_ratio), median AS (SELECT npl_ratio FROM ordered LIMIT 1 OFFSET (SELECT COUNT(*)/2 FROM ordered)) ...

Concepts

SELECT CTE JOIN Subquery

Practise the topic: SQL practice questions · CTE practice · JOIN 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

Department Roster with GROUP_CONCATMedium · FreeConsistent Director AnalysisMedium · FreeBelow Department AverageMedium · FreeHighest Total Salary Budget DepartmentMedium · FreeFare Imputation AnalysisMedium · FreeUNION ALL Dedup: Cross-Dataset SearchMedium · 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