Constituency Winners From Booth-Level Counts

Medium
83.

Constituency Winners From Booth-Level Counts

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

ColumnType
candidate_idint
candidate_namevarchar
partyvarchar
constituencyvarchar

Primary key: candidate_id.

One row per candidate; a candidate stands in exactly one constituency.

Table: booth_results

ColumnType
booth_idint
candidate_idint
votesint

Primary key (together): booth_id, candidate_id.

Votes for one candidate at one booth. A booth belongs to one constituency and only reports that constituency's candidates.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

candidates

candidate_idcandidate_namepartyconstituency
1Rahul NairLok Vikas PartyHebbal
2Priya ReddyJan Shakti MorchaHebbal
3Arjun RaoIndependentHebbal
4Meera IyerLok Vikas PartyJayanagar
5Karan SinghJan Shakti MorchaJayanagar
6Sara ThomasLok Vikas PartyBTM Layout

booth_results

booth_idcandidate_idvotes
111412
112380
11355
121301
122350
12361
214520
215470
224300
225350

Output

constituencycandidate_namepartytotal_votes
HebbalPriya ReddyJan Shakti Morcha730
JayanagarMeera IyerLok Vikas Party820
JayanagarKaran SinghJan Shakti Morcha820
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
candidate_idint
candidate_namevarchar
partyvarchar
constituencyvarchar

Primary key: candidate_id.

One row per candidate; a candidate stands in exactly one constituency.

Table: booth_results

ColumnType
booth_idint
candidate_idint
votesint

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_idcandidate_namepartyconstituency
1Rahul NairLok Vikas PartyHebbal
2Priya ReddyJan Shakti MorchaHebbal
3Arjun RaoIndependentHebbal
4Meera IyerLok Vikas PartyJayanagar
5Karan SinghJan Shakti MorchaJayanagar
6Sara ThomasLok Vikas PartyBTM Layout

booth_results

booth_idcandidate_idvotes
111412
112380
11355
121301
122350
12361
214520
215470
224300
225350

Output

constituencycandidate_namepartytotal_votes
HebbalPriya ReddyJan Shakti Morcha730
JayanagarMeera IyerLok Vikas Party820
JayanagarKaran SinghJan Shakti Morcha820

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 = 1

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