Team Dominance by Pass Success

Problem

Table: Teams

1
2
3
4
5
6
7
8


+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| player_id   | int     |
| team_name   | varchar | 
+-------------+---------+
player_id is the unique key for this table.
Each row contains the unique identifier for player and the name of one of the teams participating in that match.

Table: Passes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| pass_from   | int     |
| time_stamp  | varchar |
| pass_to     | int     |
+-------------+---------+
(pass_from, time_stamp) is the primary key for this table.
pass_from is a foreign key to player_id from Teams table.
Each row represents a pass made during a match, time_stamp represents the time in minutes (00:00-90:00) when the pass was made,
pass_to is the player_id of the player receiving the pass.

Write a solution to calculate the dominance score for each team in both halves of the match. The rules are as follows:

A match is divided into two halves: first half (00:00-45:00 minutes) and second half (45:01-90:00 minutes)
The dominance score is calculated based on successful and intercepted passes:
- When pass_to is a player from the same team: +1 point
- When pass_to is a player from the opposing team (interception): -1 point
A higher dominance score indicates better passing performance

Return the result table ordered by team_name and half_number in ascending order.

The result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58


Input:

Teams table:

+------------+-----------+
| player_id  | team_name |
+------------+-----------+
| 1          | Arsenal   |
| 2          | Arsenal   |
| 3          | Arsenal   |
| 4          | Chelsea   |
| 5          | Chelsea   |
| 6          | Chelsea   |
+------------+-----------+

Passes table:

+-----------+------------+---------+
| pass_from | time_stamp | pass_to |
+-----------+------------+---------+
| 1         | 00:15      | 2       |
| 2         | 00:45      | 3       |
| 3         | 01:15      | 1       |
| 4         | 00:30      | 1       |
| 2         | 46:00      | 3       |
| 3         | 46:15      | 4       |
| 1         | 46:45      | 2       |
| 5         | 46:30      | 6       |
+-----------+------------+---------+

Output:

+-----------+-------------+-----------+
| team_name | half_number | dominance |
+-----------+-------------+-----------+
| Arsenal   | 1           | 3         |
| Arsenal   | 2           | 1         |
| Chelsea   | 1           | -1        |
| Chelsea   | 2           | 1         |
+-----------+-------------+-----------+

Explanation:

- **First Half (00:00-45:00):**
    - Arsenal's passes:
        - 1 → 2 (00:15): Successful pass (+1)
        - 2 → 3 (00:45): Successful pass (+1)
        - 3 → 1 (01:15): Successful pass (+1)
    - Chelsea's passes:
        - 4 → 1 (00:30): Intercepted by Arsenal (-1)
- **Second Half (45:01-90:00):**
    - Arsenal's passes:
        - 2 → 3 (46:00): Successful pass (+1)
        - 3 → 4 (46:15): Intercepted by Chelsea (-1)
        - 1 → 2 (46:45): Successful pass (+1)
    - Chelsea's passes:
        - 5 → 6 (46:30): Successful pass (+1)
- The results are ordered by team_name and then half_number

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.

Problem#

Examples#

Example 1#

Solution#

Approach#

Code#

MySQL#

Oracle#

PostgreSQL#

Explanation#

Complexity#