SQL Quest › SQL Interview Questions › Subqueries & CTEs
Above-Average-Assess Properties
Find Manhattan properties whose assess_total exceeds the Manhattan average. Use a scalar subquery for the Manhattan average. Show address, bldg_class, assess_total. Order by 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 |
Expected output: Above-average Manhattan stock
Hint
WHERE borough='MN' AND assess_total > (SELECT AVG(assess_total) FROM properties WHERE borough='MN').
Concepts
SELECT WHERE Subquery
Practise the topic: SQL practice questions · CTE practice
Related questions
Department Roster with GROUP_CONCATMedium · FreeConsistent Director AnalysisMedium · FreeBelow Department AverageMedium · FreeHighest Total Salary Budget DepartmentMedium · FreeFare Imputation AnalysisMedium · FreeUNION ALL Dedup: Cross-Dataset SearchMedium · Free
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test