CPMagical CP Library

Problem solution · SQL

Longest Team Pass Streak

Longest Team Pass Streak: a SQL solution using depth-first search. Learn the idea, check the complexity, and read the full code, with credit to walkccc LeetCode Solutions.

Technique
Depth-first search
Source
walkccc LeetCode Solutions
Length
51 lines
Start with the idea.

Try the problem first. If you get stuck, read the approach below, then write your own solution. The full code is at the bottom.

Approach

Depth-first search

For Longest Team Pass Streak, the implementation follows one branch at a time, making it suitable for components, trees, backtracking, or dependency exploration.

  1. Define the state carried into one recursive or stack frame.
  2. Mark or choose the current state before exploring children.
  3. Combine child results or undo the choice when the branch finishes.

Code notes

  • 51 lines of SQL from the credited upstream file 3390.sql.
  • The implementation keeps its working state in language-native values and containers.
  • No explicit loop blocks detected.

Complexity

Count unique states for graph traversal; for backtracking, count the branching factor and maximum depth.

Check the problem constraints before deciding whether this complexity will pass.

Source

Code and credit

This code comes from walkccc LeetCode Solutions by P.-Y. Chen (walkccc) and is used under the MIT licence.

Open problem ↗Original source ↗
Full codeLongest Team Pass Streak · SQLSQL
Use this to learn the idea, then write your own version.
WITH RECURSIVE  -- Join team names for both passing and receiving players.  TeamPasses AS (    SELECT      Team1.team_name AS team1,      Team2.team_name AS team2,      Passes.time_stamp    FROM Passes    INNER JOIN Teams AS Team1      ON (Passes.pass_from = Team1.player_id)    INNER JOIN Teams AS Team2      ON (Passes.pass_to = Team2.player_id)  ),  -- Rank passes by timestamp within each team.  Ranked AS (    SELECT      team1,      team2,      RANK() OVER(PARTITION BY team1 ORDER BY time_stamp) AS `rank`    FROM TeamPasses  ),  -- Recursively calculate pass streaks.  PassStreaks AS (    -- Base case: first pass for each team    SELECT      team1,      team2,      `rank`,      IF(team1 = team2, 1, 0) AS streak    FROM Ranked    WHERE `rank` = 1    UNION ALL    -- Recursive case: subsequent passes    SELECT      Ranked.team1,      Ranked.team2,      Ranked.`rank`,      IF(Ranked.team1 = Ranked.team2, PassStreaks.streak + 1, 0) AS streak    FROM Ranked    INNER JOIN PassStreaks      ON (        Ranked.`rank` = PassStreaks.`rank` + 1        AND Ranked.team1 = PassStreaks.team1)  )-- Get the longest streak for each team.SELECT team1 AS team_name, MAX(streak) AS longest_streakFROM PassStreaksGROUP BY 1HAVING longest_streak > 0ORDER BY 1;

Keep practising

Did this explanation save you time? I'm a Grade 11 student building this free library to make difficult algorithms easier to understand.

Buy me a coffee ↗