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

Method 1 - Team Half-wise Dominance by Pass Success

Intuition

For each pass we can attribute +1 to the passer’s team when the receiver is on the same team and -1 when the receiver belongs to the opponent. We also need to split passes into two halves by parsing the minute and second from the timestamp. Summing these +1/−1 values per team and per half gives the dominance score.

Approach

Join Passes with Teams twice to get the passer’s team (team_from) and the receiver’s team (team_to).
Parse time_stamp into minutes and seconds and classify each pass into half_number = 1 (first half) or 2 (second half). First half includes all timestamps <= 45:00, second half is strictly after 45:00.
For each pass row compute score = CASE WHEN team_from = team_to THEN 1 ELSE -1 END.
Aggregate SUM(score) grouped by team_name and half_number and order the result by team_name, half_number ascending.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SELECT
  s.team_name,
  s.half_number,
  SUM(s.score) AS dominance
FROM (
  SELECT
    tf.team_name AS team_name,
    CASE
      WHEN CAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) < 45 THEN 1
      WHEN CAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) = 45
           AND CAST(SUBSTRING_INDEX(p.time_stamp, ':', -1) AS UNSIGNED) = 0 THEN 1
      ELSE 2
    END AS half_number,
    CASE WHEN tf.team_name = tt.team_name THEN 1 ELSE -1 END AS score
  FROM Passes p
  JOIN Teams tf ON p.pass_from = tf.player_id
  JOIN Teams tt ON p.pass_to = tt.player_id
) s
GROUP BY s.team_name, s.half_number
ORDER BY s.team_name ASC, s.half_number ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SELECT
  s.team_name,
  s.half_number,
  SUM(s.score) AS dominance
FROM (
  SELECT
    tf.team_name AS team_name,
    CASE
      WHEN TO_NUMBER(SUBSTR(p.time_stamp, 1, INSTR(p.time_stamp, ':') - 1)) < 45 THEN 1
      WHEN TO_NUMBER(SUBSTR(p.time_stamp, 1, INSTR(p.time_stamp, ':') - 1)) = 45
           AND TO_NUMBER(SUBSTR(p.time_stamp, INSTR(p.time_stamp, ':') + 1)) = 0 THEN 1
      ELSE 2
    END AS half_number,
    CASE WHEN tf.team_name = tt.team_name THEN 1 ELSE -1 END AS score
  FROM Passes p
  JOIN Teams tf ON p.pass_from = tf.player_id
  JOIN Teams tt ON p.pass_to = tt.player_id
) s
GROUP BY s.team_name, s.half_number
ORDER BY s.team_name ASC, s.half_number ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SELECT
  s.team_name,
  s.half_number,
  SUM(s.score) AS dominance
FROM (
  SELECT
    tf.team_name AS team_name,
    CASE
      WHEN (CAST(split_part(p.time_stamp, ':', 1) AS INTEGER) < 45) THEN 1
      WHEN (CAST(split_part(p.time_stamp, ':', 1) AS INTEGER) = 45
            AND CAST(split_part(p.time_stamp, ':', 2) AS INTEGER) = 0) THEN 1
      ELSE 2
    END AS half_number,
    CASE WHEN tf.team_name = tt.team_name THEN 1 ELSE -1 END AS score
  FROM Passes p
  JOIN Teams tf ON p.pass_from = tf.player_id
  JOIN Teams tt ON p.pass_to = tt.player_id
) s
GROUP BY s.team_name, s.half_number
ORDER BY s.team_name ASC, s.half_number ASC;

Complexity

⏰ Time complexity: O(N) – We scan the Passes table once and perform joins and a grouped aggregation over the resulting rows (N = number of passes).
🧺 Space complexity: O(T) – We materialize only the grouped intermediate results (one row per team/half), proportional to the number of teams T.

Problem#

Examples#

Example 1#

Solution#

Method 1 - Team Half-wise Dominance by Pass Success#

Intuition#

Approach#

Code#

Complexity#