Compute most popular location with weights
Company: Microsoft
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given a dataset of voting records for concert locations. Each record includes voter_id, location_text, and an optional numeric weight (default
1). Write SQL and/or Python to compute the most popular location by total vote weight. Explain how you handle ties, missing or malformed location_text, and verify correctness with a small example. Analyze the time and space complexity of your solution.
Overview: This question evaluates a candidate's ability to perform weighted aggregation, data cleaning, tie-breaking logic, and handling of malformed or missing text fields using SQL and/or Python.
You are given a table of voting records for concert locations. Each record includes a voter_id, a location_text (free-form text), and an optional numeric weight. If weight is NULL, treat it as 1.
Write an SQL query to compute the most popular location(s) by total vote weight, with the following requirements:
1) Ignore records where location_text is missing or malformed, defined as:
- location_text IS NULL, or
- location_text is an empty string '', or
- location_text consists only of whitespace characters.
2) When weight is NULL, treat it as the default value 1.
3) Aggregate the total vote weight for each valid location_text.
4) Return all location(s) whose total vote weight is equal to the maximum total vote weight (i.e., if there is a tie, return every tied location), along with that total weight.
Output columns:
- location_text
- total_weight
Additionally (no need to implement this in SQL), briefly describe how you would analyze the time and space complexity of your solution in Big-O terms, assuming an index on location_text.
Use the table definition and sample data provided below.
Tables
votes(voter_id INT, location_text VARCHAR(100), weight INT)
Hints
- Filter out rows where TRIM(location_text) is NULL or an empty string before aggregating.
- Use COALESCE(weight, 1) to apply the default weight, then GROUP BY location_text and filter to rows with the maximum SUM(weight).