SQL Quest › SQL Interview Questions › Subqueries & CTEs

ROA Outliers — Top 5% by Return

MediumFreeQuerying BasicsSubqueries & CTEsJoins

ROA anomaly screen — exceptionally high return-on-assets can be a positive signal (well-run bank) OR a fraud signal (aggressive accounting, hidden risk-taking, fee-based revenue games). Either way, the top 5% deserves a closer look. Find banks whose return_on_assets (period_end=20251231) sits in the top 5% across all top-200 banks. Show name, state, return_on_assets, total_assets. Order ROA 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: Top-5% ROA outliers

Hint

WITH q4 AS (...). Cutoff via subquery: SELECT return_on_assets FROM q4 ORDER BY return_on_assets DESC LIMIT 1 OFFSET (CAST(COUNT * 0.05 AS INTEGER)). Then filter banks above that.

Concepts

SELECT Subquery JOIN ORDER BY

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

ROA Outliers — Top 5% by Return

Schema

Hint

Concepts

In these company practice sets

Related questions