Description

Table: Candidate

Column NameType
idint
namevarchar
  • id is the column with unique values for this table.
  • Each row of this table contains information about the id and the name of a candidate.

Table: Vote

Column NameType
idint
candidateIdint
  • id is an auto-increment primary key (column with unique values).
  • candidateId is a foreign key (reference column) to id from the Candidate table.
  • Each row of this table determines the candidate who got the ith vote in the elections.

Problem Statement

Write a solution to report the name of the winning candidate (i.e., the candidate who got the largest number of votes).

The test cases are generated so that exactly one candidate wins the elections.

The result format is in the following example.

Example 1:

Input:

  • Candidate table:
idname
1A
2B
3C
4D
5E
  • Vote table:
idcandidateId
12
24
33
42
55

Output:

name
B

Explanation:

  • Candidate B has 2 votes. Candidates C, D, and E have 1 vote each.
  • The winner is candidate B.

Solution

This is typical aggregation problem. Here, the problem can be divided into following.

  1. Find the candidateId from Vote table which occurs the most. This can be done using aggregation.
  2. Associate the name to this candidateId and output only first name.
1SELECT name FROM (
2    SELECT name, COUNT(1) votes 
3        FROM Candidate
4        JOIN Vote
5        ON Candidate.id = Vote.candidateId
6        GROUP BY name 
7        ORDER BY votes DESC
8        LIMIT 1
9) tmp;