SQL Cricket Team Ranking Based on Points

Problem Statement

A cricket tournament is played between multiple teams.

• Winning team gets +1 point

• Losing team gets –1 point

We need to calculate total points for each team and find the top-ranked team based on points.

CREATE TABLE Matches (
MatchID INT PRIMARY KEY,
Team1 VARCHAR(50),
Team2 VARCHAR(50),
Winner VARCHAR(50)
);

INSERT INTO Matches (MatchID, Team1, Team2, Winner) VALUES
(1, 'India', 'Australia', 'India'),
(2, 'India', 'England', 'England'),
(3, 'Australia', 'England', 'Australia'),
(4, 'India', 'Australia', 'Australia'),
(5, 'England', 'India', 'India');

SQL Query to Calculate Team Points

WITH TeamPoints AS 
(
SELECT Winner AS Team, 1 AS Points FROM Matches 
UNION ALL
SELECT CASE WHEN Winner = Team1 THEN Team2 ELSE Team1 END AS Team, 
-1 AS Points FROM Matches 
) 
SELECT Team, SUM(Points) AS TotalPoints, RANK() OVER (ORDER BY SUM(Points) DESC) AS TeamRank 
FROM TeamPoints 
GROUP BY Team;