Election
Given a database of the results of an election, find the number of seats won by each party. There are some rules to going about this:
- There are many constituencies in a state and many candidates who are contesting the election from each constituency.
- Each candidate belongs to a party.
- The candidate with the maximum number of votes in a given constituency wins for that constituency.
The output should be in the following format: Party Seats_won.
The ordering should be in the order of seats won in descending order.
This is a SQL aggregation problem with a two-step logic: first determine the winning candidate in each constituency by selecting the maximum votes per constituency, then join those winners to the candidates table and count how many seats each party won. The final result should be grouped by party and ordered by seats_won in descending order.
END
