Hat-Trick, Как то так
SELECT
a2.team_id AS id,
a2.`name`,
MAX(a1.skill) max_skl,
(SELECT AVG(skill) FROM players WHERE team_id = a2.team_id) AS average,
(SELECT COUNT(*) FROM players WHERE team_id = a2.team_id) AS cnt
FROM players a1
JOIN teams a2 ON a2.team_id = a1.team_id
GROUP BY player_id ORDER BY max_skl DESC