SQL Quest › SQL Interview Questions › Conditional Logic

Trust and Savings Banks

MediumFreeQuerying BasicsConditional LogicAggregation & GroupingString Functions

Banking nomenclature carries info: a name with 'Trust' suggests trust/wealth-management activity; 'Savings' suggests a thrift/savings institution. Categorize each bank by trailing-name signal: 'Trust' if name contains 'Trust', 'Savings' if name contains 'Savings', 'Other' otherwise. Show name_type, bank_count, total_assets_billions (rounded to 1 decimal — total_assets is in thousands, so divide by 1000000.0 to convert to billions). Order by bank_count 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

Expected output: Trust banks vs savings banks vs general

Hint

CASE WHEN name LIKE '%Trust%' THEN 'Trust' WHEN name LIKE '%Savings%' THEN 'Savings' ELSE 'Other' END. ⚠️ For the billions conversion use 1000000.0 (with the decimal), not 1000000 — SQLite does integer division when both operands are integers and you'll lose the fractional part (e.g. 20517.8 becomes 20517).

Concepts

SELECT WHERE LIKE CASE GROUP BY String Functions

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

Trust and Savings Banks

Schema

Hint

Concepts

In these company practice sets

Related questions