Longest Team Pass Streak solution and explanation
The problem
Table: Teams
+-------------+---------+ | Column Name | Type | +-------------+---------+ | player_id | int | | team_name | varchar | +-------------+---------+ player_id is the unique key for this table. Each row contains the unique identifier for player and the name of one of the teams participating in that match.
Table: Passes
+-------------+---------+ | Column Name | Type | +-------------+---------+ | pass_from | int | | time_stamp | varchar | | pass_to | int | +-------------+---------+ (pass_from, time_stamp) is the unique key for this table. pass_from is a foreign key to player_id from Teams table. Each row represents a pass made during a match, time_stamp represents the time in minutes (00:00-90:00) when the pass was made, pass_to is the player_id of the player receiving the pass.
Write a solution to find the longest successful pass streak for each team during the match. The rules are as follows:
- A successful pass streak is defined as consecutive passes where:
- Both the
pass_fromandpass_toplayers belong to the same team
- Both the
- A streak breaks when either:
- The pass is intercepted (received by a player from the opposing team)
Return the result table ordered by team_name in ascending order.
The result format is in the following example.
Example:
Input:
Teams table:
+-----------+-----------+ | player_id | team_name | +-----------+-----------+ | 1 | Arsenal | | 2 | Arsenal | | 3 | Arsenal | | 4 | Arsenal | | 5 | Chelsea | | 6 | Chelsea | | 7 | Chelsea | | 8 | Chelsea | +-----------+-----------+
Passes table:
+-----------+------------+---------+ | 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 | +-----------+----------------+
Explanation:
- Arsenal's streaks:
- First streak: 3 passes (1→2→3→4) ended when player 4 passed to Chelsea's player 5
- Second streak: 2 passes (1→2→3)
- Longest streak = 3
- Chelsea's streaks:
- First streak: 3 passes (6→7→8→6→5)
- Longest streak = 4
Approach overview
Problem Overview: You need the longest unbroken sequence of consecutive passes for a team, where each link in the chain continues the streak. The answer is a single length, and the hard part is deciding where one streak ends and the next begins.
Approach 1: Self-Join Chain Extension (O(n^2) time, O(n) space)
Treat each pass as a candidate starting point. For each row, join against the table to find the next pass that continues the chain, then repeat until no match exists. Track the maximum chain length seen. This version is easy to reason about because it follows the streak literally, but each row can trigger a fresh walk through the data. Use it to confirm your understanding of the rules before optimizing.
Approach 2: Gaps and Islands with ROW_NUMBER (O(n log n) time, O(n) space)
Order the passes by time within each team and assign a sequential ROW_NUMBER(). Subtract that number from a second sequence that reflects the streak condition, such as a value derived from the time or pass order. Rows in the same streak produce the same derived key, so you group by that key and take the largest COUNT(*). The window function does the heavy lifting, and the whole solution runs in a single pass after the sort. Prefer this when you want a clean, set-based query that scales with table size.
Recommended for interviews: The self-join approach shows you understand the streak definition. The gaps and islands approach shows you know how to turn a sequential problem into a grouping problem, which is the skill interviewers are testing. Explain the brute force first, then move to the window function version and justify the sort cost. See SQL, window functions, and database problems for more practice with this pattern.
Solution
Thinking
We want each team's longest run of consecutive same-team passes. A cross-team pass breaks that team's streak.
After joining team names, a running sum of
same_team=0inside each team partitions the passes into streak groups.Keep only same-team rows, count by
(team,group_id), and take the per-team maximum.
WITH
PassesWithTeams AS (
SELECT
p.pass_from,
p.pass_to,
t1.team_name AS team_from,
t2.team_name AS team_to,
IF(t1.team_name = t2.team_name, 1, 0) same_team_flag,
p.time_stamp
FROM
Passes p
JOIN Teams t1 ON p.pass_from = t1.player_id
JOIN Teams t2 ON p.pass_to = t2.player_id
),
StreakGroups AS (
SELECT
team_from AS team_name,
time_stamp,
same_team_flag,
SUM(
CASE
WHEN same_team_flag = 0 THEN 1
ELSE 0
END
) OVER (
PARTITION BY team_from
ORDER BY time_stamp
) AS group_id
FROM PassesWithTeams
),
StreakLengths AS (
SELECT
team_name,
group_id,
COUNT(*) AS streak_length
FROM StreakGroups
WHERE same_team_flag = 1
GROUP BY 1, 2
),
LongestStreaks AS (
SELECT
team_name,
MAX(streak_length) AS longest_streak
FROM StreakLengths
GROUP BY 1
)
SELECT
team_name,
longest_streak
FROM LongestStreaks
ORDER BY 1;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join Chain Extension | O(n^2) | O(n) | Small tables, or to verify streak rules before optimizing |
| Gaps and Islands with ROW_NUMBER | O(n log n) | O(n) | General case and large tables, where a single sorted pass is preferred |
Frequently asked questions
Is Longest Team Pass Streak easy or hard?
It is rated Hard on FleetCode with a 40.2% acceptance rate. The difficulty comes from identifying streak boundaries, not from the syntax. Once you recognize the gaps and islands pattern, the query becomes straightforward to write.
Longest Team Pass Streak Python or Java solution?
This problem is a SQL problem, so the solution is written as a MySQL query rather than in Python or Java. The default approach on FleetCode is available in MySQL. If you want a procedural version for practice, you can simulate the same logic by sorting records and scanning them once while tracking the current streak length.
How to solve Longest Team Pass Streak in O(n log n)?
Sort passes by time per team using a window function, then compute a streak key by subtracting a sequential row number from a second sequence that marks the streak condition. Rows that share a key belong to the same streak. Group by team and key, count rows in each group, and select the maximum per team.
What is the best approach for Longest Team Pass Streak?
The best approach is gaps and islands using window functions. Assign a ROW_NUMBER() ordered by time within each team, derive a group key that stays constant across each streak, then group and take the maximum count. It runs in O(n log n) time because of the sort, with O(n) space for the intermediate results.
What data structure is used in Longest Team Pass Streak?
There is no explicit data structure beyond the relational table. The solution relies on SQL window functions, specifically ROW_NUMBER(), along with common table expressions and GROUP BY aggregation. Intermediate results are held in temporary result sets, not custom structures.
What is the time complexity of Longest Team Pass Streak?
The optimal solution runs in O(n log n) time, dominated by sorting the passes within each team. The naive self-join chain extension runs in O(n^2) time because it can rescan the table for every starting pass. Space is O(n) for the intermediate CTEs in both cases.
Is Longest Team Pass Streak asked at Google, Amazon, or Meta?
FleetCode tags this problem under General, so there is no verified company breakdown for it on the platform. The gaps and islands pattern itself appears often in SQL interviews at large tech companies, so practicing it is useful regardless of the specific problem.