📈 Report 1: Season Win/Loss Record

SELECT t.team_name, s.season_year, COUNT(m.match_id) AS total_matches, SUM(m.result = 'Win') AS wins, SUM(m.result = 'Loss') AS losses, SUM(m.result = 'Draw') AS draws, SUM(m.team_score) AS total_points_for, SUM(m.opponent_score) AS total_points_against, SUM(m.team_score) - SUM(m.opponent_score) AS point_diff, ROUND(SUM(m.result='Win') / COUNT(m.match_id) * 100, 1) AS win_pct FROM `match` m JOIN team t ON t.team_id = m.team_id JOIN season s ON s.season_id = m.season_id GROUP BY t.team_id, s.season_id ORDER BY s.season_year DESC, wins DESC;