Welcome to Subscribe On Youtube
2820. Election Results
Description
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.
Solutions
Solution 1: Window Function + Group Statistics
We can use the window function count
to calculate the number of votes each voter gives to the candidates, then use the group statistics function sum
to calculate the total number of votes for each candidate. Next, we use the window function rank
to calculate the ranking of each candidate, and finally filter out the candidate who ranks first.
Note that there may be multiple candidates ranking first in the result set, so we need to use order by
to sort the candidates.

# Write your MySQL query statement below WITH T AS ( SELECT candidate, SUM(vote) AS tot FROM ( SELECT candidate, 1 / (COUNT(candidate) OVER (PARTITION BY voter)) AS vote FROM Votes WHERE candidate IS NOT NULL ) AS t GROUP BY 1 ), P AS ( SELECT candidate, RANK() OVER (ORDER BY tot DESC) AS rk FROM T ) SELECT candidate FROM P WHERE rk = 1 ORDER BY 1;