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 AnalysisInput: Pass SequencePass ChainA1A2A3A4C5Streak breaks here!AnalyzeStreak DetectionPass AnalysisA1→A2: Same team ✓A2→A3: Same team ✓A3→A4: Same team ✓A4→C5: Different teams ✗Arsenal streak: 3ResultOutputteam_namelongest_streakArsenal3Chelsea4Window functions identify streak boundaries by detecting team possession changes
Understanding the Visualization
1
Input Tables
Teams and Passes with player-team mapping
2
JOIN Analysis
Match each pass to team ownership
3
Streak Detection
Identify consecutive same-team passes
Key Takeaway
🎯 Key Insight: Use window functions to detect possession changes and group consecutive passes by team

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