Team Internal Pass Streak Maxima

Hard

A sports analytics platform tracks passing events from a single football match and needs each team's best uninterrupted sequence of internal ball movement. Table: Teams player_id: unique player identifier team_name: team label for that player Table: Passes pass_from: player_id of the passer time_stamp: event time in zero-padded HH:MM style within match range 00:00 to 90:00 pass_to: player_id of the receiver Streak model (per team): Consider only pass rows where pass_from belongs to that team. A pass is successful for that team if pass_to is also from the same team. Every successful pass increments the current streak by 1. An intercepted pass (receiver from another team) resets the current streak to 0. longest_streak is the maximum value reached by the current streak during the match. Task: Return one row per team with its longest_streak. Output requirements: Return columns in this exact order: team_name, longest_streak Sort rows by team_name ascending. If a team has no successful internal pass, return longest_streak = 0. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 1         | Arsenal   |
| 2         | Arsenal   |
| 3         | Arsenal   |
| 4         | Arsenal   |
| 5         | Chelsea   |
| 6         | Chelsea   |
| 7         | Chelsea   |
| 8         | Chelsea   |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 1         | 00:05      | 2       |
| 2         | 00:07      | 3       |
| 3         | 00:08      | 4       |
| 4         | 00:10      | 5       |
| 6         | 00:15      | 7       |
| 7         | 00:17      | 8       |
| 8         | 00:20      | 6       |
| 6         | 00:22      | 5       |
| 1         | 00:25      | 2       |
| 2         | 00:27      | 3       |

Output

[
  {"team_name":"Arsenal","longest_streak":3},
  {"team_name":"Chelsea","longest_streak":4}
]

Explanation

Arsenal's first internal run has 3 passes before interception. Chelsea records a 4-pass uninterrupted internal sequence.

Example 2

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 10        | Falcons   |
| 11        | Falcons   |
| 20        | Rockets   |
| 21        | Rockets   |
| 30        | Wolves    |
| 31        | Wolves    |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 10        | 01:00      | 20      |
| 11        | 01:05      | 21      |
| 20        | 01:10      | 21      |
| 21        | 01:15      | 30      |

Output

[
  {"team_name":"Falcons","longest_streak":0},
  {"team_name":"Rockets","longest_streak":1},
  {"team_name":"Wolves","longest_streak":0}
]

Explanation

Falcons only make intercepted passes, Rockets have one successful internal pass, and Wolves have no outgoing pass rows.

Example 3

Input

Teams:
| player_id | team_name |
|-----------|-----------|
| 100       | North     |
| 101       | North     |
| 102       | North     |
| 200       | South     |
| 201       | South     |
| 202       | South     |

Passes:
| pass_from | time_stamp | pass_to |
|-----------|------------|---------|
| 100       | 12:30      | 101     |
| 101       | 12:30      | 200     |
| 102       | 12:31      | 100     |
| 200       | 12:40      | 201     |
| 201       | 12:41      | 202     |
| 202       | 12:42      | 101     |
| 200       | 12:43      | 201     |

Output

[
  {"team_name":"North","longest_streak":1},
  {"team_name":"South","longest_streak":2}
]

Explanation

North's success at 12:30 is immediately reset by another North-origin pass at the same time that is intercepted. South reaches a best run of 2 before reset.

Constraints

1 <= number of rows in Teams <= 200000
0 <= number of rows in Passes <= 300000
player_id values are unique in Teams
1 <= player_id <= 1000000000
team_name is a non-empty string
For every pass row, pass_from and pass_to reference existing player_id values
(pass_from, time_stamp) is unique in Passes
time_stamp format is zero-padded HH:MM in range 00:00 to 90:00
For one team, process only rows where pass_from belongs to that team
A successful pass requires pass_from and pass_to from the same team
An intercepted pass resets only that originating team's current streak
If a team has no successful internal pass, longest_streak is 0
Output columns must be exactly: team_name, longest_streak
Output must be sorted by team_name ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [team_name, longest_streak]

Teams: | player_id | team_name | |-----------|-----------| | 1 | Arsenal | | 2 | Arsenal | | 3 | Arsenal | | 4 | Arsenal | | 5 | Chelsea | | 6 | Chelsea | | 7 | Chelsea | | 8 | Chelsea | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 1 | 00:05 | 2 | | 2 | 00:07 | 3 | | 3 | 00:08 | 4 | | 4 | 00:10 | 5 | | 6 | 00:15 | 7 | | 7 | 00:17 | 8 | | 8 | 00:20 | 6 | | 6 | 00:22 | 5 | | 1 | 00:25 | 2 | | 2 | 00:27 | 3 |

Teams: | player_id | team_name | |-----------|-----------| | 10 | Falcons | | 11 | Falcons | | 20 | Rockets | | 21 | Rockets | | 30 | Wolves | | 31 | Wolves | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 10 | 01:00 | 20 | | 11 | 01:05 | 21 | | 20 | 01:10 | 21 | | 21 | 01:15 | 30 |

Teams: | player_id | team_name | |-----------|-----------| | 100 | North | | 101 | North | | 102 | North | | 200 | South | | 201 | South | | 202 | South | Passes: | pass_from | time_stamp | pass_to | |-----------|------------|---------| | 100 | 12:30 | 101 | | 101 | 12:30 | 200 | | 102 | 12:31 | 100 | | 200 | 12:40 | 201 | | 201 | 12:41 | 202 | | 202 | 12:42 | 101 | | 200 | 12:43 | 201 |