Cities Above National Housing Average

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Listings =

[{"listing_id":113,"city":"LosAngeles","price":7560386},{"listing_id":136,"city":"SanFrancisco","price":2380268},{"listing_id":92,"city":"Chicago","price":9833209},{"listing_id":60,"city":"Chicago","price":5147582},{"listing_id":8,"city":"Chicago","price":5274441},{"listing_id":79,"city":"SanFrancisco","price":8372065},{"listing_id":37,"city":"Chicago","price":7939595},{"listing_id":53,"city":"LosAngeles","price":4965123},{"listing_id":178,"city":"SanFrancisco","price":999207},{"listing_id":51,"city":"NewYork","price":5951718},{"listing_id":121,"city":"NewYork","price":2893760}]

Cities Above National Housing Average

Hints

Cities Above National Housing Average

Hints