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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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