This problem has been asked in Telstra interview. I personally solved it in the interview.
Table Name : MatchResults
| Column Name | Type |
|---|---|
| id | int |
| team1 | varchar |
| team2 | varchar |
| won | varchar |
id is the primary key for this table.
Each row contains information about the matches played between the two teams and who won the match.
Write a SQL query to find the number of matches won and lost for all the teams.
Return the result table in decreasing order of the number of matches won by them.
The query result format is in the following example:
Input
Table Name : MatchResults
| id | team1 | team2 | won |
|---|---|---|---|
| 1 | India | Australia | India |
| 2 | Australia | England | Australia |
| 3 | England | India | India |
| 4 | South Africa | Australia | Australia |
| 5 | England | South Africa | South Africa |
| 6 | India | South Africa | India |
| 7 | Sri Lanka | India | India |
| 8 | Australia | Sri Lanka | Australia |
| 9 | Sri Lanka | England | Sri Lanka |
| 10 | South Africa | Sri Lanka | South Africa |
Output
| Team | Played | Won | Lost |
|---|---|---|---|
| India | 4 | 4 | 0 |
| Australia | 4 | 3 | 1 |
| South Africa | 4 | 2 | 2 |
| Sri Lanka | 4 | 1 | 3 |
| England | 4 | 0 | 4 |