Team Internal Pass Streak Maxima

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Teams =

[{"player_id":1,"team_name":"Arsenal"},{"player_id":2,"team_name":"Arsenal"},{"player_id":3,"team_name":"Arsenal"},{"player_id":4,"team_name":"Arsenal"},{"player_id":5,"team_name":"Chelsea"},{"player_id":6,"team_name":"Chelsea"},{"player_id":7,"team_name":"Chelsea"},{"player_id":8,"team_name":"Chelsea"}]

Passes =

[{"pass_from":1,"time_stamp":"00:05","pass_to":2},{"pass_from":2,"time_stamp":"00:07","pass_to":3},{"pass_from":3,"time_stamp":"00:08","pass_to":4},{"pass_from":4,"time_stamp":"00:10","pass_to":5},{"pass_from":6,"time_stamp":"00:15","pass_to":7},{"pass_from":7,"time_stamp":"00:17","pass_to":8},{"pass_from":8,"time_stamp":"00:20","pass_to":6},{"pass_from":6,"time_stamp":"00:22","pass_to":5},{"pass_from":1,"time_stamp":"00:25","pass_to":2},{"pass_from":2,"time_stamp":"00:27","pass_to":3}]

Team Internal Pass Streak Maxima

Hints

Team Internal Pass Streak Maxima

Hints