SQL Quest › SQL Interview Questions › Subqueries & CTEs

Banks with NPL Above 2-Sigma

HardProQuerying BasicsSubqueries & CTEsAggregation & Grouping

Statistical anomaly detection — the first technique in any fraud analyst's toolkit. Flag banks whose npl_ratio (period_end=20251231) sits more than 2 standard deviations above the mean across all top-200 banks. This Z-score-based cutoff is exactly what regulators use as a first-pass screen for credit-quality outliers — banks worth investigating further. Compute mean and stddev of npl_ratio, then return banks above the 2-sigma cutoff. Show name, state, npl_ratio, z_score (rounded to 2 decimals). Order by z_score 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

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

financials

cert	name	period_end	total_assets	total_deposits	total_equity	net_income	net_loans	loan_loss_allowance	npl_ratio	tier1_capital_ratio	return_on_assets	return_on_equity
628	JPMORGAN CHASE BANK NA	20251231	3752662000	2697842000	335936000	49644000	1471951000	25539000	0.3618498015542034	15.29035880700036	1.3448553188805723	15.32
3510	BANK OF AMERICA NA	20251231	2636823000	2101968000	246250000	30380000	1166885000	13188000	0.38356764940232996	12.471807379783922	1.1544462063028051	12.24
7213	CITIBANK NATIONAL ASSN	20251231	1836436000	1465300000	176530000	15178000	700763000	17160000	0.30254253347244336	14.327392160721764	0.8458257679165101	8.67

Expected output: Banks 2+ sigma above the mean — outliers

Hint

This is a Pro challenge — the hint, the step-by-step tutor and the reference solution open in the app.

Concepts

SELECT CTE Aggregation Subquery Statistics

Practise the topic: SQL practice questions · CTE practice · GROUP BY exercises · Advanced SQL interview questions

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.

Banks with NPL Above 2-Sigma

Schema

Hint

Concepts

In these company practice sets

Related questions