Watch the video solution for Election Results, a medium level problem. This walkthrough by Everyday Data Science has 610 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Votes
+-------------+---------+ | Column Name | Type | +-------------+---------+ | voter | varchar | | candidate | varchar | +-------------+---------+ (voter, candidate) is the primary key (combination of unique values) for this table. Each row of this table contains name of the voter and their candidate.
The election is conducted in a city where everyone can vote for one or more candidates or choose not to vote. Each person has 1 vote so if they vote for multiple candidates, their vote gets equally split across them. For example, if a person votes for 2 candidates, these candidates receive an equivalent of 0.5 votes each.
Write a solution to find candidate who got the most votes and won the election. Output the name of the candidate or If multiple candidates have an equal number of votes, display the names of all of them.
Return the result table ordered by candidate in ascending order.
The result format is in the following example.
Example 1:
Input: Votes table: +----------+-----------+ | voter | candidate | +----------+-----------+ | Kathy | null | | Charles | Ryan | | Charles | Christine | | Charles | Kathy | | Benjamin | Christine | | Anthony | Ryan | | Edward | Ryan | | Terry | null | | Evelyn | Kathy | | Arthur | Christine | +----------+-----------+ Output: +-----------+ | candidate | +-----------+ | Christine | | Ryan | +-----------+ Explanation: - Kathy and Terry opted not to participate in voting, resulting in their votes being recorded as 0. Charles distributed his vote among three candidates, equating to 0.33 for each candidate. On the other hand, Benjamin, Arthur, Anthony, Edward, and Evelyn each cast their votes for a single candidate. - Collectively, Candidate Ryan and Christine amassed a total of 2.33 votes, while Kathy received a combined total of 1.33 votes. Since Ryan and Christine received an equal number of votes, we will display their names in ascending order.
Problem Overview: You are given election vote records and need to determine the winning candidate for each constituency. The result should identify the candidate with the highest number of votes within each constituency, handling ties correctly if multiple candidates share the top count.
Approach 1: Aggregation + Self Join (O(n log n) time, O(n) space)
Start by counting votes per candidate within each constituency using GROUP BY constituency, candidate. This produces a table containing vote totals for every candidate in every constituency. Next, compute the maximum vote count for each constituency using another aggregation. Finally, join the aggregated candidate counts with the maximum vote counts and keep rows where the candidate's vote total equals the constituency maximum. The approach relies on SQL aggregation and GROUP BY operations. Time complexity is O(n log n) due to grouping and sorting in the database engine, and space complexity is O(n) for intermediate grouped results.
Approach 2: Window Function + Group Statistics (O(n log n) time, O(n) space)
This method performs the vote counting and ranking in a single query pipeline. First, aggregate votes per candidate using GROUP BY constituency, candidate. Then apply a window function such as RANK() OVER (PARTITION BY constituency ORDER BY vote_count DESC). The PARTITION BY clause isolates each constituency while the ordering ranks candidates by vote totals. Rows with rank = 1 represent the winner(s) for that constituency. This approach is concise and avoids explicit joins while naturally handling ties. It relies heavily on window functions and grouped statistics, making it the most expressive and maintainable solution for analytical SQL queries. Database engines internally perform sorting per partition, resulting in O(n log n) time complexity and O(n) intermediate storage.
Recommended for interviews: The window function approach is usually preferred. It demonstrates strong SQL fluency and familiarity with analytical queries such as RANK(), DENSE_RANK(), and PARTITION BY. Showing the aggregation + join approach first proves you understand how vote counts are computed, while the window-function solution shows you can write concise, production-grade SQL.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + Self Join | O(n log n) | O(n) | When window functions are unavailable or when you want explicit aggregation logic |
| Window Function + Group Statistics | O(n log n) | O(n) | Best for modern SQL engines like MySQL 8+, concise ranking within each constituency |
