Allocating votes from pooled elections


Suppose you're running a school election, and are looking to tally up the results. The election is structured as follows:

  • Groups of students are split into voting pools, each with unique IDs. The voting pools are assigned a number of votes to allocate based on how many students are in the given pool
  • A voting pool can vote multiple times, but if they do their votes will be split evenly amongst the candidates

The voting information is stored in two tables (shown below): 'election_data' and 'voter_pools':

  • election_data contains each voting pool's votes for the given candidates. You'll notice many pools voted for multiple candidates
  • voter_pools contains the number of votes allocated to each unique pool

Given this information, write a SQL query to return the winner of the election, along with their associated votes.

Table: election_data

voting_pool_id candidate
9992 Susan
9992 Jerry
12993 Susan
12993 Lisa
96673 Chris
9992 Chris
1411 Tom
1117 Tom
96673 Allen
96673 Lisa

Table: voter_pools

voting_pool_id num_votes
9992 65
12993 30
96673 70
1411 65
1117 75

Click here to view/query the tables in an interactive SQL editor.


Access restricted

Subscribe to premium account to see the solution.

Get premium now