Team Dominance by Pass Success - Problem

You are given two tables: Teams and Passes. Write a SQL query to calculate the dominance score for each team in both halves of a football match.

Rules:

  • A match is divided into two halves: first half (00:00-45:00 minutes) and second half (45:01-90:00 minutes)
  • The dominance score is calculated based on successful and intercepted passes:
    • When pass_to is a player from the same team: +1 point
    • When pass_to is a player from the opposing team (interception): -1 point
  • Return results ordered by team_name and half_number in ascending order

A higher dominance score indicates better passing performance for that team in that half.

Table Schema

Teams
Column Name Type Description
player_id PK int Unique identifier for each player
team_name varchar Name of the team the player belongs to
Primary Key: player_id
Passes
Column Name Type Description
pass_from PK int Player ID who made the pass
time_stamp PK varchar Time when pass was made (MM:SS format)
pass_to int Player ID who received the pass
Primary Key: (pass_from, time_stamp)

Input & Output

Example 1 — Basic Match Analysis
Input Tables:
Teams
player_id team_name
1 Arsenal
2 Arsenal
3 Arsenal
4 Chelsea
5 Chelsea
6 Chelsea
Passes
pass_from time_stamp pass_to
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:
team_name half_number dominance
Arsenal 1 3
Arsenal 2 1
Chelsea 1 -1
Chelsea 2 1
💡 Note:

First Half (00:00-45:00):

  • Arsenal: 1→2 (+1), 2→3 (+1), 3→1 (+1) = +3
  • Chelsea: 4→1 (-1, intercepted) = -1

Second Half (45:01-90:00):

  • Arsenal: 2→3 (+1), 3→4 (-1, intercepted), 1→2 (+1) = +1
  • Chelsea: 5→6 (+1) = +1
Example 2 — All Interceptions
Input Tables:
Teams
player_id team_name
1 TeamA
2 TeamB
Passes
pass_from time_stamp pass_to
1 10:00 2
2 50:00 1
Output:
team_name half_number dominance
TeamA 1 -1
TeamB 2 -1
💡 Note:

Both passes were intercepted by the opposing team, resulting in negative dominance scores for both teams in their respective halves.

Constraints

  • 1 ≤ player_id ≤ 1000
  • time_stamp format is 'MM:SS' where 00:00 ≤ time_stamp ≤ 90:00
  • team_name consists of alphanumeric characters only
  • Each pass has valid pass_from and pass_to player IDs

Visualization

Tap to expand
Team Dominance by Pass Success INPUT DATA time team event 10:00 TeamA pass_success 15:30 TeamB pass_success 20:00 TeamA interception 25:00 TeamA pass_success 50:00 TeamB pass_success 60:00 TeamA pass_success 75:00 TeamB interception First Half 00:00 - 45:00 Second Half 45:01 - 90:00 Scoring Rules pass_success = +1 interception = -1 ALGORITHM STEPS 1 Parse Time Convert time to minutes "10:00" --> 10 mins 2 Determine Half Check if time <= 45 time <= 45 ? half=1 time > 45 ? half=2 3 Calculate Score Sum per team per half score[team][half] += pass ? +1 : -1 4 Sort Results Order by team, half ORDER BY team, half GROUP BY team_name, half_number FINAL RESULT team half score TeamA 1 +1 TeamA 2 +1 TeamB 1 +1 TeamB 2 0 Score Breakdown TeamA Half 1: +1(pass) -1(int) +1(pass) = 1 TeamA Half 2: +1(pass) = 1 TeamB Half 1: +1(pass) = 1 OK Key Insight: Use conditional aggregation with CASE WHEN to assign +1 for successful passes and -1 for interceptions. Determine half by checking if time <= 45 minutes. Group by team and half, then order alphabetically. TutorialsPoint - Team Dominance by Pass Success | Optimal Solution

Related Problems

Asked in
ESPN 23 FanDuel 18 DraftKings 15
23.4K Views
Medium Frequency
~18 min Avg. Time
847 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