Half-by-Half Team Passing Control Index

Hard

A football analytics platform tracks every completed or intercepted pass and wants a half-level control metric per team. Table: Teams player_id: unique player identifier team_name: team label for that player in this match Table: Passes pass_from: player_id of the passer time_stamp: pass time in MM:SS format between 00:00 and 90:00 pass_to: player_id of the receiving player Scoring model: If pass_to belongs to the same team as pass_from: +1 If pass_to belongs to the opposite team: -1 Match halves: Half 1 includes times 00:00 through 45:00 (inclusive) Half 2 includes times 45:01 through 90:00 (inclusive) Task: Compute each team's dominance score per half, based on passes initiated by that team. Output requirements: Return columns in this exact order: team_name, half_number, dominance Return only (team_name, half_number) combinations where the team made at least one pass in that half. Sort rows by team_name ascending, then half_number ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 1         | Storm     |
| 2         | Storm     |
| 3         | Storm     |
| 4         | Pulse     |
| 5         | Pulse     |
| 6         | Pulse     |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 1         | 00:15      | 2       |
| 2         | 12:40      | 3       |
| 3         | 44:59      | 4       |
| 4         | 20:05      | 5       |
| 2         | 45:00      | 1       |
| 5         | 46:10      | 6       |
| 6         | 47:00      | 1       |
| 1         | 45:01      | 3       |

Output

[
  {"team_name":"Pulse","half_number":1,"dominance":1},
  {"team_name":"Pulse","half_number":2,"dominance":0},
  {"team_name":"Storm","half_number":1,"dominance":2},
  {"team_name":"Storm","half_number":2,"dominance":1}
]

Explanation

Each pass contributes +1 or -1 to the passer's team in the corresponding half. 45:00 is first half, while 45:01 is second half.

Example 2

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 11        | Atlas     |
| 12        | Atlas     |
| 13        | Blaze     |
| 14        | Blaze     |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 11        | 05:00      | 12      |
| 12        | 30:00      | 13      |
| 11        | 10:10      | 12      |
| 13        | 60:00      | 14      |
| 14        | 75:30      | 11      |

Output

[
  {"team_name":"Atlas","half_number":1,"dominance":1},
  {"team_name":"Blaze","half_number":2,"dominance":0}
]

Explanation

Atlas only appears in half 1 because Atlas players make no second-half passes. Blaze only appears in half 2.

Example 3

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 21        | Cedar     |
| 22        | Cedar     |
| 23        | Delta     |
| 24        | Delta     |
| 25        | Ember     |
| 26        | Ember     |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 21        | 00:00      | 22      |
| 22        | 10:00      | 23      |
| 23        | 15:00      | 24      |
| 24        | 45:00      | 26      |
| 25        | 45:01      | 26      |
| 26        | 50:00      | 21      |
| 23        | 70:15      | 22      |
| 22        | 88:20      | 21      |

Output

[
  {"team_name":"Cedar","half_number":1,"dominance":0},
  {"team_name":"Cedar","half_number":2,"dominance":1},
  {"team_name":"Delta","half_number":1,"dominance":0},
  {"team_name":"Delta","half_number":2,"dominance":-1},
  {"team_name":"Ember","half_number":2,"dominance":0}
]

Explanation

Boundary times split the halves exactly at 45:00 and 45:01, and output includes only observed (team, half) passing combinations.

Constraints

2 <= number of rows in Teams <= 200000
1 <= number of rows in Passes <= 300000
Teams.player_id is unique
(Passes.pass_from, Passes.time_stamp) is unique
Passes.pass_from and Passes.pass_to both reference Teams.player_id
team_name is a non-empty string
time_stamp format is MM:SS
00:00 <= time_stamp <= 90:00
Half 1 is 00:00 through 45:00 inclusive
Half 2 is 45:01 through 90:00 inclusive
Each pass contributes +1 if same-team, otherwise -1
Aggregate by passer team and match half
Return only observed (team_name, half_number) passing groups
Output columns must be: team_name, half_number, dominance
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

Teams: | player_id | team_name | |-----------|-----------| | 1 | Storm | | 2 | Storm | | 3 | Storm | | 4 | Pulse | | 5 | Pulse | | 6 | Pulse | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 1 | 00:15 | 2 | | 2 | 12:40 | 3 | | 3 | 44:59 | 4 | | 4 | 20:05 | 5 | | 2 | 45:00 | 1 | | 5 | 46:10 | 6 | | 6 | 47:00 | 1 | | 1 | 45:01 | 3 |

[ {"team_name":"Pulse","half_number":1,"dominance":1}, {"team_name":"Pulse","half_number":2,"dominance":0}, {"team_name":"Storm","half_number":1,"dominance":2}, {"team_name":"Storm","half_number":2,"dominance":1} ]

Teams: | player_id | team_name | |-----------|-----------| | 11 | Atlas | | 12 | Atlas | | 13 | Blaze | | 14 | Blaze | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 11 | 05:00 | 12 | | 12 | 30:00 | 13 | | 11 | 10:10 | 12 | | 13 | 60:00 | 14 | | 14 | 75:30 | 11 |

Teams: | player_id | team_name | |-----------|-----------| | 21 | Cedar | | 22 | Cedar | | 23 | Delta | | 24 | Delta | | 25 | Ember | | 26 | Ember | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 21 | 00:00 | 22 | | 22 | 10:00 | 23 | | 23 | 15:00 | 24 | | 24 | 45:00 | 26 | | 25 | 45:01 | 26 | | 26 | 50:00 | 21 | | 23 | 70:15 | 22 | | 22 | 88:20 | 21 |

[ {"team_name":"Cedar","half_number":1,"dominance":0}, {"team_name":"Cedar","half_number":2,"dominance":1}, {"team_name":"Delta","half_number":1,"dominance":0}, {"team_name":"Delta","half_number":2,"dominance":-1}, {"team_name":"Ember","half_number":2,"dominance":0} ]