SQL Quest › SQL Interview Questions › Joins

Average Tier 1 Ratio Per State

MediumFreeQuerying BasicsJoinsAggregation & Grouping

For each state, compute the average tier1_capital_ratio across its banks (using the most recent quarter, period_end=20251231). Tier 1 ratio is the regulatory capital adequacy metric — banks need to stay above 6% (well-capitalized: 8%). Only show states with at least 3 banks so the average is meaningful. Show state, bank_count, avg_tier1 (rounded to 2 decimals). Order by avg_tier1 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: States with strongest capital

Hint

JOIN institutions on cert; GROUP BY state HAVING COUNT(*) >= 3; ROUND(AVG(tier1_capital_ratio), 2).

Concepts

SELECT JOIN GROUP BY Aggregation HAVING JOIN + GROUP BY

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

In these company practice sets

JPMorgan · Bloomberg

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

Average Tier 1 Ratio Per State

Schema

Hint

Concepts

In these company practice sets

Related questions