1. Winning Candidate

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

1706463563255311 Image scaled to 85%

Output

1706536192335012 Image scaled to 23%

Try It YourSelf

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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    |
+------+