Find Expensive Cities - Problem

Given a Listings table with property listings and their prices by city, find all cities where the average home price exceeds the national average.

The result should be sorted by city name in ascending order.

Table Structure:

  • listing_id: Unique identifier for each listing
  • city: City name where the property is located
  • price: Price of the property

Table Schema

Listings
Column Name Type Description
listing_id PK int Unique identifier for each property listing
city varchar Name of the city where the property is located
price int Price of the property in the listing
Primary Key: listing_id
Note: Each row represents one property listing with its location and price

Input & Output

Example 1 — Cities Above National Average
Input Table:
listing_id city price
1 New York 800
2 Los Angeles 600
3 New York 1200
4 Austin 400
5 Los Angeles 700
Output:
city
New York
💡 Note:

National average: (800+600+1200+400+700)/5 = 740. City averages: New York = (800+1200)/2 = 1000 ✓, Los Angeles = (600+700)/2 = 650 ✗, Austin = 400 ✗. Wait, let me recalculate: Los Angeles = 650 < 740, so only New York qualifies. Actually Los Angeles average is 650 which is less than 740, so the output should be just New York.
Example 2 — Single Listings Per City
Input Table:
listing_id city price
1 Boston 900
2 Chicago 500
3 Miami 700
Output:
city
Boston
💡 Note:

National average: (900+500+700)/3 = 700. Since each city has only one listing, city averages equal the listing prices. Boston (900) and Miami (700) are >= 700, while Chicago (500) is below average.
Example 3 — No Cities Above Average
Input Table:
listing_id city price
1 Dallas 300
2 Phoenix 400
3 Dallas 500
Output:
city
💡 Note:

National average: (300+400+500)/3 = 400. City averages: Dallas = (300+500)/2 = 400, Phoenix = 400. Since we need cities that exceed the national average, no cities qualify (both equal but don't exceed).

Constraints

  • 1 ≤ listing_id ≤ 10^5
  • 1 ≤ city.length ≤ 50
  • 1 ≤ price ≤ 10^6
  • City names contain only letters and spaces

Visualization

Tap to expand
Find Expensive Cities INPUT Listings Table city property price NYC Apt A 800000 NYC Apt B 900000 LA House 1 750000 Chicago Condo 350000 Miami Villa 650000 SF Loft 1200000 Multiple listings per city with varying prices NYC LA CHI SF 6 total listings ALGORITHM STEPS 1 Calculate National Avg AVG(price) over all listings = 775,000 (national avg) 2 Group By City Calculate AVG per city NYC: 850,000 LA: 750,000 Chicago: 350,000 SF: 1,200,000 NYC > 775K [OK] LA < 775K CHI < 775K SF > 775K [OK] 3 HAVING clause filter city_avg > national_avg HAVING AVG(price) > (SELECT AVG(price) FROM...) 4 ORDER BY city ASC Sort alphabetically A-Z NYC SF (sorted) FINAL RESULT Cities with avg price > national avg city NYC SF Price Comparison 775K (nat avg) NYC: 850K SF: 1.2M Output Array: ["NYC", "SF"] 2 expensive cities found Key Insight: Use a subquery to calculate the national average, then compare each city's average against it. GROUP BY groups listings by city, HAVING filters groups after aggregation (unlike WHERE). The subquery runs once and is compared against each grouped city average. O(n) time complexity. TutorialsPoint - Find Expensive Cities | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
23.4K Views
Medium Frequency
~12 min Avg. Time
867 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen