Price per square foot by city
mediumPrice per square foot by city
Zillow SQL Interview Question
Zillow compares cities by the typical asking price per square foot. Some listings don't show a price yet, and they shouldn't count.
For each city, return the number of listings that have a price as priced_listings and the average price per square foot of those listings as avg_price_per_sqft, rounded to 2 decimal places. Sort by avg_price_per_sqft, highest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
listingsTable16 rows
| Column Name | Type |
|---|---|
| listing_id | BIGINT |
| city | VARCHAR |
| bedrooms | BIGINT |
| square_feet | BIGINT |
| list_price | BIGINT |
| listed_date | DATE |
| status | VARCHAR |
listingsExample Input
| listing_id | city | bedrooms | square_feet | list_price | listed_date | status |
|---|---|---|---|---|---|---|
| 30001 | Seattle | 3 | 1850 | 925000 | 2024-04-01 | active |
| 30002 | Seattle | 2 | 1100 | NULL | 2024-04-02 | active |
| 30003 | Denver | 4 | 2400 | 780000 | 2024-04-02 | pending |
| 30004 | Austin | 3 | 1700 | NULL | 2024-04-03 | active |
| 30005 | Denver | 2 | 1050 | NULL | 2024-04-04 | active |
| 30006 | Austin | 4 | 2600 | 640000 | 2024-04-05 | sold |
| 30007 | Seattle | 1 | 720 | NULL | 2024-04-05 | active |
| 30008 | Phoenix | 3 | 1600 | 455000 | 2024-04-06 | active |
| 30009 | Denver | 3 | 1500 | 515000 | 2024-04-07 | active |
| 30010 | Austin | 2 | 980 | NULL | 2024-04-08 | pending |
| 30011 | Phoenix | 4 | 2200 | 599000 | 2024-04-08 | active |
| 30012 | Seattle | 3 | 1900 | 1010000 | 2024-04-09 | sold |
| 30013 | Austin | 3 | 1450 | 389000 | 2024-04-10 | active |
| 30014 | Denver | 5 | 3100 | NULL | 2024-04-11 | active |
| 30015 | Phoenix | 2 | 900 | 310000 | 2024-04-12 | active |
| 30016 | Seattle | 4 | 2500 | NULL | 2024-04-12 | pending |
Example Output
| city | priced_listings | avg_price_per_sqft |
|---|---|---|
| Seattle | 2 | 515.79 |
| Denver | 2 | 334.17 |
| Phoenix | 3 | 300.36 |
| Austin | 2 | 257.21 |
Explanation
Seattle has 5 listings, but only 2 show a price: 925,000 for 1,850 square feet, which is 500.00 per square foot, and 1,010,000 for 1,900 square feet, which is 531.58. Their average is 515.79.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Zillow
Difficulty
medium
Topic
null handling
Language
SQL
Your query
Starting the SQL engine...