SQL Quest › SQL Interview Questions › Subqueries & CTEs

Active and Recently Failed

MediumFreeQuerying BasicsSubqueries & CTEs

Build a unified table of active vs recently failed banks. UNION the institutions table (label='ACTIVE') with failures since 2020 (label='FAILED'). Show name, state, status for both. Limit each side to 20 rows. Order by status, then name.

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

failures

cert	name	city	state	failure_date	pre_failure_assets	pre_failure_deposits	resolution_type	estimated_loss
57488	METROPOLITAN CAPITAL B&T	CHICAGO	NULL	1/30/2026	261185	212152	FAILURE	NULL
5520	SANTA ANNA NATIONAL BANK	SANTA ANNA	NULL	6/27/2025	76938	71376	FAILURE	23460
28611	PULASKI SAVINGS BANK	CHICAGO	NULL	1/17/2025	49466	42741	FAILURE	30284

Expected output: Combined view: 20 active + 20 failed

Hint

Two SELECTs UNION ALL. Labels via constant string. SUBSTR for year filter on failures.

Concepts

SELECT UNION ALL WHERE Set Operations

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

Active and Recently Failed

Schema

Hint

Concepts

In these company practice sets

Related questions