Longest Team Pass Streak - Problem

You are given two tables: Teams and Passes. Find the longest successful pass streak for each team during the match.

A successful pass streak is defined as consecutive passes where:

  • Both the pass_from and pass_to players belong to the same team
  • A streak breaks when the pass is intercepted (received by a player from the opposing team)

Return the result table ordered by team_name in ascending order.

Table Schema

Teams
Column Name Type Description
player_id PK int Unique identifier for player (primary key)
team_name varchar Name of the team the player belongs to
Primary Key: player_id
Passes
Column Name Type Description
pass_from PK int Player ID who made the pass (foreign key to Teams.player_id)
time_stamp PK varchar Time in minutes (00:00-90:00) when the pass was made
pass_to int Player ID who received the pass
Primary Key: (pass_from, time_stamp)

Input & Output

Example 1 — Basic Pass Streaks
Input Tables:
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 longest_streak
Arsenal 3
Chelsea 4
💡 Note:

Arsenal's streaks: First streak has 3 consecutive passes (1→2→3→4) but breaks when player 4 passes to Chelsea's player 5. Second streak has 2 passes (1→2→3). Longest = 3.

Chelsea's streaks: One streak has 4 consecutive passes (6→7→8→6→5). Longest = 4.
Example 2 — Single Pass Streaks
Input Tables:
Teams
player_id team_name
1 TeamA
2 TeamB
3 TeamA
4 TeamB
Passes
pass_from time_stamp pass_to
1 00:05 2
2 00:07 3
3 00:09 4
Output:
team_name longest_streak
TeamA 0
TeamB 0
💡 Note:

All passes are intercepted (between different teams), so no successful streaks exist. Each team has a longest streak of 0.

Constraints

  • 1 ≤ player_id ≤ 100
  • team_name contains only letters and spaces
  • time_stamp format is MM:SS where 00:00 ≤ time_stamp ≤ 90:00
  • Each pass has a unique combination of (pass_from, time_stamp)

Visualization

Tap to expand
Longest Team Pass Streak INPUT Teams Table team_id | team_name 1 | Red Team 2 | Blue Team 3 | Green Team Passes Table pass_id|time|from_id|to_id 1 |01 |P1(R) |P2(R) 2 |02 |P2(R) |P3(R) 3 |03 |P3(R) |P4(R) 4 |04 |P4(R) |P5(B) 5 |05 |P5(B) |P6(B) 6 |06 |P6(B) |P7(B) Red Team Blue Team R=Red, B=Blue (intercept) ALGORITHM STEPS 1 Join Tables Link passes with player teams (from and to) 2 Detect Streak Breaks Mark when from_team != to_team (intercept) 3 Create Streak Groups Use window SUM to assign group IDs 4 Find Max Per Team GROUP BY team, get MAX(streak_length) Streak Detection Red: 3 passes Blue: 2 passes X break FINAL RESULT Output Table team_name streak Blue Team 2 Green Team 1 Red Team 3 Streak Visualization Red 3 Blue 2 Green 1 OK Ordered by team_name ASC Key Insight: Use window functions with cumulative SUM of "break flags" to create streak group identifiers. A break flag = 1 when pass_from team differs from pass_to team (interception detected). Then COUNT passes per group and take MAX per team to find longest consecutive streak. TutorialsPoint - Longest Team Pass Streak | Optimal Solution

Related Problems

Asked in
Amazon 23 Meta 18 Google 15
23.7K Views
Medium Frequency
~25 min Avg. Time
847 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen