SQL Quest › SQL Interview Questions › Conditional Logic

Building Age Tier

EasyFreeQuerying BasicsConditional Logic

Categorize each Manhattan property by age_tier: 'Pre-War' if year_built < 1945, 'Mid-Century' if 1945-1979, 'Modern' if 1980-1999, 'Contemporary' if 2000+. Skip properties with year_built = 0 (unknown). The borough column uses 'MN' for Manhattan. Show address, year_built, age_tier. Order by year_built ASC, then address alphabetically for ties.

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

properties

bbl	borough	address	zip	bldg_class	land_use	year_built	num_floors	units_residential	units_total	lot_area_sqft	bldg_area_sqft	assess_total	owner_name	latitude	longitude
1009720001	MN	240 1 AVENUE	10009	D7	4	1945	13.0000000	8764	8812	2675000	8942176	790924050.00000	BPP ST OWNER LLC	40.7317236	-73.9778964
1009950005	MN	1472 BROADWAY	10036	O4	5	1998	51.0000000	0	2	45800	1642675	489648150.00000	NYC ECONOMIC DEVELOPMENT CORPORATION	40.7560214	-73.9857642
1010070029	MN	1345 AVENUE OF THE AMER	10105	O9	5	1968	49.0000000	0	50	90375	1931978	429758550.00000	1345 LEASEHOLD LLC	40.7630634	-73.9792541

Expected output: Each labeled Pre-War / Mid-Century / Modern / Contemporary

Hint

CASE WHEN year_built < 1945 THEN 'Pre-War' WHEN year_built < 1980 THEN 'Mid-Century' WHEN year_built < 2000 THEN 'Modern' ELSE 'Contemporary' END

Concepts

SELECT CASE WHERE

Practise the topic: SQL practice questions · CASE WHEN practice

Building Age Tier

Schema

Hint

Concepts

Related questions