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]

Loading problem...

Preparing your editor. You can read the problem while it loads.

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]