SQL Quest › SQL Interview Questions › Aggregation & Grouping

Failure Clustering — State + Year

MediumFreeQuerying BasicsAggregation & GroupingDate Functions

Regional failure waves — bank failures cluster in time and place: Texas savings & loan crisis 1980s, Florida real-estate bust 2008-2010, regional bank crisis 2023 (SVB, Signature, First Republic, all California/New York). Cluster failures by (state, year) and find groups where 3 or more banks failed in the same state in the same year — the post-crisis pattern. Show state, failure_year, failure_count, total_pre_failure_assets. Order by failure_count desc, then failure_year desc. Top 10. (failure_date is text in M/D/YYYY format — extract year via SUBSTR last 4 chars.)

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

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: Hot zones of bank failure

Hint

CAST(SUBSTR(failure_date, -4) AS INTEGER) extracts year from M/D/YYYY. Skip failure_date that's NULL or '0'.

Concepts

SELECT GROUP BY HAVING Date Functions

Practise the topic: SQL practice questions · GROUP BY exercises · Date function 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.

Failure Clustering — State + Year

Schema

Hint

Concepts

In these company practice sets

Related questions