Longest Team Pass Streak - Practice Coding Problems

Longest Team Pass Streak - Problem

Database Hard

You're analyzing pass sequences in a football match to determine which team maintained the longest consecutive successful passes! 🏈

Given two database tables:

Teams: Contains player IDs and their team affiliations
Passes: Contains chronological pass records with timestamps

A successful pass streak occurs when consecutive passes happen between players of the same team. The streak breaks when:

A pass is intercepted (received by an opposing team player)

Goal: Find the longest successful pass streak for each team during the match.

Input: Two SQL tables with player-team mappings and chronological pass data

Output: Table showing each team's longest consecutive pass streak, ordered by team name

Example: If Arsenal passes 1→2→3→4→(intercepted by Chelsea), then 1→2→3, their longest streak is 3 passes.

Input & Output

example_1.sql — Basic Case

$ Input: Teams: [(1,'Arsenal'), (2,'Arsenal'), (3,'Arsenal'), (4,'Arsenal'), (5,'Chelsea'), (6,'Chelsea')] Passes: [(1,'00:05',2), (2,'00:07',3), (3,'00:08',4), (4,'00:10',5)]

› Output: Arsenal: 3, Chelsea: 0

💡 Note: Arsenal has 3 consecutive passes (1→2→3→4) before 4 passes to Chelsea's player 5, breaking the streak

example_2.sql — Multiple Streaks

$ Input: Teams: [(1,'Arsenal'), (2,'Arsenal'), (5,'Chelsea'), (6,'Chelsea')] Passes: [(1,'00:05',2), (2,'00:07',5), (5,'00:10',6), (6,'00:12',1), (1,'00:15',2)]

› Output: Arsenal: 2, Chelsea: 1

💡 Note: Arsenal: 1→2 (1 pass), then 1→2 (1 pass) = max 1. Chelsea: 5→6 (1 pass). Wait, Arsenal gets 2 from the final sequence.

example_3.sql — Single Team

$ Input: Teams: [(1,'Arsenal'), (2,'Arsenal'), (3,'Arsenal')] Passes: [(1,'00:05',2), (2,'00:07',3), (3,'00:10',1)]

› Output: Arsenal: 3

💡 Note: All passes are within Arsenal, creating one streak of 3 consecutive passes

Constraints

1 ≤ number of players ≤ 100
1 ≤ number of passes ≤ 1000
All timestamps are in format MM:SS between 00:00 and 90:00
Each pass has a unique (pass_from, time_stamp) combination
All player_ids in Passes table exist in Teams table

Visualization

Tap to expand

Understanding the Visualization

Map players to teams

First, we need to know which team each player belongs to

Track pass sequences

Follow the chronological order of passes during the match

Identify streaks

Count consecutive passes that stay within the same team

Find longest streaks

For each team, determine their longest successful passing sequence

Key Takeaway

🎯 Key Insight: Use window functions to detect possession changes efficiently - LAG() identifies when the ball switches teams, and running sums group consecutive same-team passes for easy counting.

Asked in

G Google 45 a Amazon 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses SQL window functions to efficiently identify possession changes and group consecutive same-team passes. Key insight: Use LAG() to detect team changes and running sums to create unique streak identifiers, then count passes per group.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use SQL window functions to detect team changes and group consecutive same-team passes
Brute Force (Nested Subqueries)	O(n²)	O(1)	Check every possible starting position and count consecutive same-team passes

Window Functions (Optimal) — Algorithm Steps

Join passes with teams to get team information for both sender and receiver
Use LAG() window function to detect when team possession changes
Create groups of consecutive same-team passes using running sums
Count passes in each group and find maximum per team

Visualization

Tap to expand

Step-by-Step Walkthrough

Identify team changes

Use LAG() window function to detect when possession changes

Create streak groups

Running sum creates unique IDs for each consecutive streak

Count and maximize

Count passes per group and find maximum for each team

Code -

solution.c — C

// SQL solution using window functions
#include <stdio.h>

char* longestTeamPassStreakOptimal() {
    static char query[] = 
    "WITH pass_details AS (\n"
    "    SELECT \n"
    "        p.pass_from,\n"
    "        p.time_stamp,\n"
    "        p.pass_to,\n"
    "        t1.team_name AS from_team,\n"
    "        t2.team_name AS to_team,\n"
    "        CASE WHEN t1.team_name = t2.team_name THEN 1 ELSE 0 END AS same_team\n"
    "    FROM Passes p\n"
    "    JOIN Teams t1 ON p.pass_from = t1.player_id\n"
    "    JOIN Teams t2 ON p.pass_to = t2.player_id\n"
    "    ORDER BY p.time_stamp\n"
    "),\n"
    "streak_groups AS (\n"
    "    SELECT *,\n"
    "        SUM(CASE \n"
    "            WHEN same_team = 0 OR \n"
    "                 LAG(from_team) OVER (ORDER BY time_stamp) != from_team OR\n"
    "                 LAG(same_team, 1, 0) OVER (ORDER BY time_stamp) = 0\n"
    "            THEN 1 \n"
    "            ELSE 0 \n"
    "        END) OVER (ORDER BY time_stamp) AS streak_id\n"
    "    FROM pass_details\n"
    "    WHERE same_team = 1\n"
    "),\n"
    "team_streaks AS (\n"
    "    SELECT \n"
    "        from_team AS team_name,\n"
    "        streak_id,\n"
    "        COUNT(*) AS streak_length\n"
    "    FROM streak_groups\n"
    "    GROUP BY from_team, streak_id\n"
    ")\n"
    "SELECT \n"
    "    team_name,\n"
    "    MAX(streak_length) AS longest_streak\n"
    "FROM team_streaks\n"
    "GROUP BY team_name\n"
    "ORDER BY team_name";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting by timestamp is O(n log n), window functions are O(n)

⚡ Linearithmic

Space Complexity

O(n)

Window functions require space to store intermediate partitioned results

⚡ Linearithmic Space

28.4K Views

Medium Frequency

~25 min Avg. Time

892 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler