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 AnalysisInput: Match DataPass Analysis1→2 (00:15): Arsenal→Arsenal✓ Same team: +14→1 (00:30): Chelsea→Arsenal✗ Interception: -1Half 1 scores calculated...GROUP BYteam, halfOutput: Dominance ScoresteamhalfdominanceArsenal1+3Chelsea1-1Key LogicFirst Half: 00:00 - 45:00Second Half: 45:01 - 90:00Same Team Pass: +1 pointIntercepted Pass: -1 pointHigher dominance = Better passing performance
Understanding the Visualization
1
Input Tables
Teams and Passes data
2
JOIN Analysis
Match passes with team information
3
Dominance Score
Calculate +1/-1 scores by half
Key Takeaway
🎯 Key Insight: Use double JOINs to compare passer and receiver teams for accurate dominance scoring

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