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
Los Angeles
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
Miami
💡 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: Problem OverviewInput: All ListingsNYC: 800LA: 600NYC: 1200Austin: 400LA: 700Nat'l Avg: 740GROUP BY& HAVINGCity AveragesNYC1000✓LA650✗Austin400✗FILTER &ORDER BYFinal ResultNYCOnly cities with average price > national average (740)
Understanding the Visualization
1
Input
Property listings with cities and prices
2
Group & Filter
GROUP BY city, calculate averages, HAVING > national avg
3
Output
Cities sorted alphabetically
Key Takeaway
🎯 Key Insight: Use HAVING to filter groups after aggregation - perfect for comparing group statistics to overall statistics

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