Allocating votes from pooled elections
Question
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 fiddle.