Team Dominance by Pass Success - Problem
You're analyzing a football match to determine which team has better passing performance during each half of the game!
Given two tables - Teams (containing player-team mappings) and Passes (containing all pass attempts with timestamps), calculate a dominance score for each team in both halves based on their passing success rate.
Scoring System:
- Successful pass (to teammate):
+1 point - Intercepted pass (to opponent):
-1 point
Match Structure:
- First Half: 00:00 - 45:00 minutes
- Second Half: 45:01 - 90:00 minutes
Return results ordered by team name and half number in ascending order. A higher dominance score indicates superior ball control and passing accuracy!
Input & Output
basic_match.sql — Basic Match Analysis
$
Input:
Teams: [(1,'Arsenal'), (2,'Arsenal'), (3,'Arsenal'), (4,'Chelsea'), (5,'Chelsea'), (6,'Chelsea')]
Passes: [(1,'00:15',2), (2,'00:45',3), (3,'01:15',1), (4,'00:30',1), (2,'46:00',3), (3,'46:15',4), (1,'46:45',2), (5,'46:30',6)]
›
Output:
[('Arsenal',1,3), ('Arsenal',2,1), ('Chelsea',1,-1), ('Chelsea',2,1)]
💡 Note:
Arsenal dominates first half with 3 successful passes vs Chelsea's 1 interception. Second half is more balanced with both teams having mixed results.
perfect_game.sql — Perfect Passing
$
Input:
Teams: [(1,'Madrid'), (2,'Madrid'), (3,'Barcelona'), (4,'Barcelona')]
Passes: [(1,'10:00',2), (2,'20:00',1), (3,'50:00',4), (4,'60:00',3)]
›
Output:
[('Barcelona',2,2), ('Madrid',1,2)]
💡 Note:
Both teams have perfect passing with no interceptions. Madrid scores 2 in first half, Barcelona scores 2 in second half.
interception_heavy.sql — High Interception Game
$
Input:
Teams: [(1,'Team A'), (2,'Team A'), (3,'Team B'), (4,'Team B')]
Passes: [(1,'30:00',3), (3,'35:00',1), (2,'50:00',4), (4,'55:00',2)]
›
Output:
[('Team A',1,-1), ('Team A',2,-1), ('Team B',1,-1), ('Team B',2,-1)]
💡 Note:
A chaotic game where every pass is intercepted by the opposing team, resulting in negative dominance scores for both teams.
Constraints
- 1 ≤ number of teams ≤ 22
- 1 ≤ number of players per team ≤ 20
- 1 ≤ number of passes ≤ 104
- All timestamps are in format MM:SS where 00:00 ≤ MM:SS ≤ 90:00
- pass_from and pass_to will always be valid player_ids
- A player cannot pass to themselves (pass_from ≠ pass_to)
Visualization
Tap to expand
Understanding the Visualization
1
Data Collection
Gather all pass events with timestamps and participant players
2
Team Mapping
Map each player involved in passes to their respective teams
3
Half Classification
Categorize passes into first half (0-45 min) or second half (45-90 min)
4
Score Calculation
Award +1 for successful passes, -1 for interceptions
5
Aggregation
Sum scores by team and half to determine dominance
Key Takeaway
🎯 Key Insight: Efficient SQL JOINs allow us to process thousands of pass events in a single query, making real-time football analytics possible.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code