Cities Above National Housing Average

Easy

A real estate intelligence team wants to identify markets that are priced above the country-wide baseline. Table: Listings listing_id: unique identifier for a property listing city: city where the listing belongs price: listing price for that property Task: Find every city whose average listing price is strictly greater than the national average listing price. Definitions: city_average(city) = average(price) over rows from that city national_average = average(price) over all rows in Listings A city qualifies if city_average(city) > national_average Return exactly one column in this order: city Sort the output by city in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Listings:
| 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"},
  {"city":"LosAngeles"}
]

Explanation

The national average is 6122059.45. Chicago and LosAngeles have higher city averages than that baseline.

Example 2

Input

Listings:
| listing_id | city   | price |
|------------|--------|-------|
| 1          | Alpha  | 10    |
| 2          | Alpha  | 20    |
| 3          | Beta   | 15    |

Output

[]

Explanation

Alpha average is 15 and Beta average is 15, exactly equal to national average 15, so no city qualifies.

Example 3

Input

Listings:
| listing_id | city   | price |
|------------|--------|-------|
| 101        | Austin | 300   |
| 102        | Austin | 500   |
| 103        | Boston | 100   |
| 104        | Boston | 200   |
| 105        | Cairo  | 600   |

Output

[
  {"city":"Austin"},
  {"city":"Cairo"}
]

Explanation

National average is 340. Austin average is 400 and Cairo average is 600, both above national; Boston is below.

Constraints

1 <= number of rows in Listings <= 200000
1 <= listing_id <= 1000000000
listing_id is unique
city is a non-empty string
1 <= price <= 1000000000
Use strict comparison city_average > national_average
Return exactly one column: city
Sort by city ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named city

Listings: | 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 |

Listings: | listing_id | city | price | |------------|--------|-------| | 101 | Austin | 300 | | 102 | Austin | 500 | | 103 | Boston | 100 | | 104 | Boston | 200 | | 105 | Cairo | 600 |