1. Winning Candidate

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

1. Winning Candidate

Table of Contents

Contents are not accessible

Problem

Table: Candidate

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
+-------------+----------+
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 Name | Type |
+-------------+------+
| id          | int  |
| candidateId | int  |
+-------------+------+
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 Definition

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.

Example

Output

Try It YourSelf

MYSQL

. . . .

Solution

To determine the candidate with the highest vote count in the election, a SQL query combines "Candidate" and "Vote" tables using a left join. This ensures inclusion of all candidates, and results are grouped by candidate name. The COUNT function calculates votes per candidate, and results are ordered in descending order. Using DISTINCT ensures unique candidate names and LIMIT 1 retrieves the top candidate. This approach offers an efficient method to identify the candidate with the maximum votes.

-- TODO: Write your user queries here
SELECT DISTINCT name
FROM   Candidate c
       LEFT JOIN Vote v
              ON v.candidateid = c.id
GROUP  BY 1
ORDER  BY Count(v.id) DESC
LIMIT  1

Let's go through the query step by step:

Step 1: JOIN Operation

Left join is performed on Candidate and vote tables.

Output After Step 1:

+----+------+--------+-----------------+
| c.id | c.name | v.id | v.candidateId |
+----+------+--------+-----------------+
| 1    | A      | NULL | NULL          |
| 2    | B      | 1    | 2             |
| 2    | B      | 4    | 2             |
| 3    | C      | 3    | 3             |
| 4    | D      | 2    | 4             |
| 5    | E      | 5    | 5             |
+------+--------+------+---------------+

Step 2: GROUP BY

GROUP BY 1

At this step, a group on the records is performed.

Output After Step 2:

+------+
| name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+

Step 3: ORDER BY

ORDER BY count(v.id) desc

COUNT(v.id) calculates the count of occurrences of the v.id column for each row in the result set. It essentially counts how many times each value of v.id appears in the data.

DESC keyword is short for "descending," and it's used to specify the sorting order. When you use DESC, the rows will be sorted in descending order.

Output After Step 3:

+------+
| name |
+------+
| B    |
| C    |
| D    |
| E    |
| A    |
+------+

Step 4: LIMIT

LIMIT 1

It will retrieve the first row of a result set

Final Output:

+------+
| name |
+------+
| B    |
+------+

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible