Problem

Given tables for football teams, matches, and passes, find the team(s) that dominated all their matches by having a higher pass success rate than their opponent in every match they played. Return the team_id and team_name of such teams, ordered by team_id.

Assume the following schema:

Table: Teams

Column Name Type
team_id int
team_name varchar

Table: Matches

Column Name Type
match_id int
host_team int
guest_team int

Table: Passes

Column Name Type
match_id int
team_id int
pass_success_rate float

Solution

Approach

For each match, compare the pass success rate of both teams. A team dominates a match if its pass success rate is strictly higher than its opponent. A team is dominant if it dominates all matches it played. We aggregate for each team and select those that dominated all their matches.

Code

MySQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

WITH team_matches AS (
  SELECT m.match_id, m.host_team AS team_id, p1.pass_success_rate AS team_rate, p2.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
  UNION ALL
  SELECT m.match_id, m.guest_team AS team_id, p2.pass_success_rate AS team_rate, p1.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
)
SELECT t.team_id, t.team_name
FROM Teams t
JOIN (
  SELECT team_id
  FROM team_matches
  GROUP BY team_id
  HAVING SUM(team_rate > opp_rate) = COUNT(*)
) d ON t.team_id = d.team_id
ORDER BY t.team_id;
Oracle
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

WITH team_matches AS (
  SELECT m.match_id, m.host_team AS team_id, p1.pass_success_rate AS team_rate, p2.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
  UNION ALL
  SELECT m.match_id, m.guest_team AS team_id, p2.pass_success_rate AS team_rate, p1.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
)
SELECT t.team_id, t.team_name
FROM Teams t
JOIN (
  SELECT team_id
  FROM team_matches
  GROUP BY team_id
  HAVING SUM(CASE WHEN team_rate > opp_rate THEN 1 ELSE 0 END) = COUNT(*)
) d ON t.team_id = d.team_id
ORDER BY t.team_id;
PostgreSQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

WITH team_matches AS (
  SELECT m.match_id, m.host_team AS team_id, p1.pass_success_rate AS team_rate, p2.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
  UNION ALL
  SELECT m.match_id, m.guest_team AS team_id, p2.pass_success_rate AS team_rate, p1.pass_success_rate AS opp_rate
  FROM Matches m
  JOIN Passes p1 ON m.match_id = p1.match_id AND m.host_team = p1.team_id
  JOIN Passes p2 ON m.match_id = p2.match_id AND m.guest_team = p2.team_id
)
SELECT t.team_id, t.team_name
FROM Teams t
JOIN (
  SELECT team_id
  FROM team_matches
  GROUP BY team_id
  HAVING SUM(CASE WHEN team_rate > opp_rate THEN 1 ELSE 0 END) = COUNT(*)
) d ON t.team_id = d.team_id
ORDER BY t.team_id;

Explanation

For each match, we compare the pass success rate of each team to its opponent. We count the number of matches where the team had a higher rate, and select teams where this count equals the total matches played. This means the team dominated all its matches.

Complexity

  • ⏰ Time complexity: O(N) where N is the number of rows in Passes/Matches.
  • 🧺 Space complexity: O(T) where T is the number of teams.