Problem

Example 1:

Solution

Method 1 – Window Functions (SQL) and Grouping (Pandas)

Intuition

We want to find the longest streak of consecutive passes between the same team members. This is a classic problem of finding the longest consecutive segment with the same value, which can be solved using window functions in SQL or groupby with diff in Pandas.

Approach

  • MySQL/PostgreSQL:
    1. Use window functions to assign a group id whenever the (passer, receiver) pair changes.
    2. For each group, count the length of the streak.
    3. Return the maximum streak and the corresponding team members.
  • Python (Pandas):
    1. Sort the data by time (if needed).
    2. Use groupby and diff to identify streaks where (passer, receiver) do not change.
    3. Use cumsum to assign group ids and then aggregate to find the longest streak.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT passer, receiver, MAX(streak) AS max_streak
FROM (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY time) -
    ROW_NUMBER() OVER (PARTITION BY passer, receiver ORDER BY time) AS grp,
    COUNT(*) OVER (PARTITION BY passer, receiver, 
      ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY passer, receiver ORDER BY time)) AS streak
  FROM Passes
) t
GROUP BY passer, receiver;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT passer, receiver, MAX(streak) AS max_streak
FROM (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY time) -
    ROW_NUMBER() OVER (PARTITION BY passer, receiver ORDER BY time) AS grp,
    COUNT(*) OVER (PARTITION BY passer, receiver, 
      ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY passer, receiver ORDER BY time)) AS streak
  FROM Passes
) t
GROUP BY passer, receiver;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import pandas as pd

def longest_team_pass_streak(df: pd.DataFrame) -> pd.DataFrame:
    df = df.sort_values('time')
    grp = (df[['passer', 'receiver']] != df[['passer', 'receiver']].shift()).any(axis=1).cumsum()
    df['grp'] = grp
    streaks = df.groupby(['passer', 'receiver', 'grp']).size().reset_index(name='streak')
    result = streaks.groupby(['passer', 'receiver'])['streak'].max().reset_index()
    result = result.rename(columns={'streak': 'max_streak'})
    return result

Complexity

  • ⏰ Time complexity: O(n), as we scan the table once and use window/groupby operations.
  • 🧺 Space complexity: O(n), for storing group ids and intermediate results.