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

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

[{"language":"MySQL","code":"SELECT\n  s.team_name,\n  s.half_number,\n  SUM(s.score) AS dominance\nFROM (\n  SELECT\n    tf.team_name AS team_name,\n    CASE\n      WHEN CAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) < 45 THEN 1\n      WHEN CAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) = 45\n           AND CAST(SUBSTRING_INDEX(p.time_stamp, ':', -1) AS UNSIGNED) = 0 THEN 1\n      ELSE 2\n    END AS half_number,\n    CASE WHEN tf.tea

Related Tags