Find Expensive Cities

Find Expensive Cities

What is this problem about?

The Find Expensive Cities interview question is a database management task that requires analyzing price data across different geographical locations. You are typically given a table containing listings of properties or products with their associated prices and the city they belong to. Your goal is to identify cities where the average price of all items is higher than the overall national (or global) average price. The output should be a list of these "expensive" cities, often sorted alphabetically or by the price difference.

Why is this asked in interviews?

Companies like Google ask this Find Expensive Cities coding problem to evaluate a candidate's proficiency in SQL aggregation and subqueries. It tests your ability to calculate a summary statistic (the global average) and then use that statistic as a filter for grouped data. This is a fundamental skill for data analysis, backend engineering, and business intelligence roles, where identifying outliers or top-performing segments is a common requirement.

Algorithmic pattern used

This problem follows the Database, Aggregation, and Subquery interview pattern. The standard approach involves:

1. Using a subquery or a Common Table Expression (CTE) to calculate the AVG() price across the entire dataset.
2. Grouping the main table by city using the GROUP BY clause.
3. Calculating the AVG() price per city.
4. Applying the HAVING clause to compare the city average against the result of the global average subquery.

Example explanation

Imagine a table Listings:

• New York: $1,000,000
• New York: $800,000
• Austin: $400,000
• Austin: $500,000
• Chicago: $600,000

Step-by-step:

1. Global Average: $(1,000,000 + 800,000 + 400,000 + 500,000 + 600,000) / 5 = 660,000$.
2. City Averages:
   • New York: $(1,000,000 + 800,000) / 2 = 900,000$.
   • Austin: $450,000$.
   • Chicago: $600,000$.
3. Filtering: New York (900,000) > Global Avg (660,000). Result: New York.

Common mistakes candidates make

• WHERE vs. HAVING: Attempting to filter the average price in the WHERE clause instead of HAVING. Remember, WHERE filters rows before grouping, while HAVING filters results after aggregation.
• Inefficient Subqueries: Calculating the global average inside the SELECT for every row instead of calculating it once as a standalone scalar value.
• Null Handling: Not considering how the database handles NULL values in the price column, which can skew averages.

Interview preparation tip

Practice using Window Functions like AVG() OVER() as an alternative to subqueries. While a subquery is more traditional, window functions can sometimes be more readable and performant in complex analytical queries.