Problem

Table: Listings

1
2
3
4
5
6
7
8
9

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| listing_id  | int     |
| city        | varchar |
| price       | int     |
+-------------+---------+
listing_id is column of unique values for this table.
This table contains listing_id, city, and price.

Write a solution to find cities where the average home prices exceed the national average home price.

Return the result table sorted bycity inascending order .

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Input: 
Listings table:
+------------+--------------+---------+
| listing_id | city         | price   | 
+------------+--------------+---------+
| 113        | LosAngeles   | 7560386 | 
| 136        | SanFrancisco | 2380268 |     
| 92         | Chicago      | 9833209 | 
| 60         | Chicago      | 5147582 | 
| 8          | Chicago      | 5274441 |  
| 79         | SanFrancisco | 8372065 | 
| 37         | Chicago      | 7939595 | 
| 53         | LosAngeles   | 4965123 | 
| 178        | SanFrancisco | 999207  | 
| 51         | NewYork      | 5951718 | 
| 121        | NewYork      | 2893760 | 
+------------+--------------+---------+
**Output**
+------------+
| city       | 
+------------+
| Chicago    | 
| LosAngeles |  
+------------+
**Explanation**
The national average home price is $6,122,059.45. Among the cities listed:
- Chicago has an average price of $7,048,706.75
- Los Angeles has an average price of $6,277,754.5
- San Francisco has an average price of $3,900,513.33
- New York has an average price of $4,422,739
Only Chicago and Los Angeles have average home prices exceeding the national average. Therefore, these two cities are included in the output table. The output table is sorted in ascending order based on the city names.

Solution

Method 1 – Aggregation with HAVING Clause

Intuition

To find cities with average home prices above the national average, we compare each city’s average price to the overall average. SQL’s aggregation functions make this direct and efficient.

Approach

  1. Calculate the national average home price using the AVG(price) function over all listings.
  2. Group listings by city and compute each city’s average price.
  3. Use the HAVING clause to filter cities whose average price exceeds the national average.
  4. Sort the result by city in ascending order.

Code

1
2
3
4
5

SELECT city
FROM Listings
GROUP BY city
HAVING AVG(price) > (SELECT AVG(price) FROM Listings)
ORDER BY city ASC;
1
2
3
4
5

SELECT city
FROM Listings
GROUP BY city
HAVING AVG(price) > (SELECT AVG(price) FROM Listings)
ORDER BY city ASC;
1
2
3
4
5

def find_expensive_cities(df: 'pd.DataFrame') -> 'pd.DataFrame':
    avg_price = df['price'].mean()
    city_avg = df.groupby('city', as_index=False)['price'].mean()
    ans = city_avg[city_avg['price'] > avg_price][['city']].sort_values('city').reset_index(drop=True)
    return ans

Complexity

  • ⏰ Time complexity: O(n) – Each row is scanned a constant number of times for aggregation and filtering.
  • 🧺 Space complexity: O(c) – Where c is the number of unique cities, for storing group-by results.