SQL Quest › SQL Interview Questions › Subqueries & CTEs

Active Properties with Permits

HardProQuerying BasicsSubqueries & CTEsJoinsAggregation & Grouping

Find properties with 2 or more issued permits. Use a CTE that groups permits by bbl + counts. JOIN to properties for borough + address + assess_total. Show bbl, address, borough, assess_total, permit_count. Order by permit_count descending, then assess_total descending. Top 15.

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

permits

permit_id	job_id	bbl	borough	permit_type	permit_status	work_type	filing_date	issuance_date	expiration_date	bldg_type	owner_business_name	owner_business_type	permittee_business_name
1688411	401749756	4006730016	QUEENS	SG	ISSUED	NULL	01/08/2004	01/08/2004	06/30/2004	2	HIGH PERFORMANCE FITNESS CENTER	PARTNERSHIP	PAUL SIGNS INC
1756781	301820661	3026080079	BROOKLYN	EW	ISSUED	MH	09/02/2004	09/10/2004	09/10/2005	2	NULL	2022-05-09 00:00:00	APEX REALTY CO. LLC 7183915300 O
3136	402213289	4095540029	QUEENS	SG	ISSUED	NULL	01/30/2006	04/11/2006	06/30/2006	2	Doral Bank: 7188507290 2022	INDIVIDUAL	WNITECH DESIGN INC DBA SPACESIGN

Expected output: BBLs with the most ISSUED permits

Hint

This is a Pro challenge — the hint, the step-by-step tutor and the reference solution open in the app.

Concepts

SELECT CTE JOIN GROUP BY Subquery Multi-CTE

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

Active Properties with Permits

Schema

Hint

Concepts

Related questions