Constituency Winners From Booth-Level Counts — SQL Problem
On counting day each booth reports the votes of every candidate standing in its constituency.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #83
Problem statement
On counting day each booth reports the votes of every candidate standing in its constituency. A candidate's total is the sum over all booths; a candidate with no booth rows has no counted votes yet.
For each constituency with counted votes, return the candidate(s) with the highest total — if two candidates tie for the top, return both — with columns constituency, candidate_name, party and total_votes. Return the rows in any order.
Tables
Table: candidates
| Column | Type |
|---|---|
| candidate_id | int |
| candidate_name | varchar |
| party | varchar |
| constituency | varchar |
Primary key: candidate_id.
One row per candidate; a candidate stands in exactly one constituency.
Table: booth_results
| Column | Type |
|---|---|
| booth_id | int |
| candidate_id | int |
| votes | int |
Primary key: booth_id, candidate_id.
Votes for one candidate at one booth. A booth belongs to one constituency and only reports that constituency's candidates.
Examples
Example 1
candidates
| candidate_id | candidate_name | party | constituency |
|---|---|---|---|
| 1 | Rahul Nair | Lok Vikas Party | Hebbal |
| 2 | Priya Reddy | Jan Shakti Morcha | Hebbal |
| 3 | Arjun Rao | Independent | Hebbal |
| 4 | Meera Iyer | Lok Vikas Party | Jayanagar |
| 5 | Karan Singh | Jan Shakti Morcha | Jayanagar |
| 6 | Sara Thomas | Lok Vikas Party | BTM Layout |
booth_results
| booth_id | candidate_id | votes |
|---|---|---|
| 11 | 1 | 412 |
| 11 | 2 | 380 |
| 11 | 3 | 55 |
| 12 | 1 | 301 |
| 12 | 2 | 350 |
| 12 | 3 | 61 |
| 21 | 4 | 520 |
| 21 | 5 | 470 |
| 22 | 4 | 300 |
| 22 | 5 | 350 |
Output
| constituency | candidate_name | party | total_votes |
|---|---|---|---|
| Hebbal | Priya Reddy | Jan Shakti Morcha | 730 |
| Jayanagar | Meera Iyer | Lok Vikas Party | 820 |
| Jayanagar | Karan Singh | Jan Shakti Morcha | 820 |
How to solve Constituency Winners From Booth-Level Counts
There are two steps, so a CTE keeps them apart. The first joins candidates to their booth rows and sums the votes per candidate. The inner join means a candidate with no booth rows has no total, and a constituency where counting has not started has no rows at all — it drops out, as the statement says.
The second step picks the leader per constituency. RANK() OVER (PARTITION BY constituency ORDER BY total_votes DESC) numbers candidates within their constituency from the top; tied totals share a rank, so filtering rnk = 1 returns both candidates of a dead heat. ROW_NUMBER() would silently pick one of them, which is exactly the bug a returning officer cannot afford.
The alternatives keep a total when it equals the constituency's maximum (a correlated MAX), or when no candidate of the same constituency has more (NOT EXISTS). All three agree on ties. The window version sorts the totals once per constituency; the correlated ones re-read the small totals set for each candidate, which is fine at this size.
Reference solution (MySQL)
WITH totals AS (
SELECT c.candidate_id, c.candidate_name, c.party, c.constituency, SUM(r.votes) AS total_votes
FROM candidates c
JOIN booth_results r ON r.candidate_id = c.candidate_id
GROUP BY c.candidate_id, c.candidate_name, c.party, c.constituency
)
SELECT constituency, candidate_name, party, total_votes
FROM (
SELECT t.*, RANK() OVER (PARTITION BY constituency ORDER BY total_votes DESC) AS rnk
FROM totals t
) ranked
WHERE rnk = 1Another way
WITH totals AS (
SELECT c.candidate_id, c.candidate_name, c.party, c.constituency, SUM(r.votes) AS total_votes
FROM candidates c JOIN booth_results r ON r.candidate_id = c.candidate_id
GROUP BY c.candidate_id, c.candidate_name, c.party, c.constituency
)
SELECT t.constituency, t.candidate_name, t.party, t.total_votes
FROM totals t
WHERE t.total_votes = (SELECT MAX(x.total_votes) FROM totals x WHERE x.constituency = t.constituency)Another way
WITH totals AS (
SELECT c.candidate_id, c.candidate_name, c.party, c.constituency, SUM(r.votes) AS total_votes
FROM candidates c JOIN booth_results r ON r.candidate_id = c.candidate_id
GROUP BY c.candidate_id, c.candidate_name, c.party, c.constituency
)
SELECT t.constituency, t.candidate_name, t.party, t.total_votes
FROM totals t
WHERE NOT EXISTS (SELECT 1 FROM totals x WHERE x.constituency = t.constituency AND x.total_votes > t.total_votes)← Grievances Resolved Within the Seven-Day SLA · Recurring Donors Across Three Months of 2024 →