+-------------+---------+
|Column Name |Type|+-------------+---------+
| player_id | int || team_name | varchar |+-------------+---------+
player_id is the uniquekeyfor this table.
Eachrowcontains the unique identifier for player and the name of one of the teams participating in that match.
Table: Passes
1
2
3
4
5
6
7
8
9
10
11
+-------------+---------+
|Column Name |Type|+-------------+---------+
| pass_from | int || time_stamp | varchar || pass_to | int |+-------------+---------+
(pass_from, time_stamp) is the primarykeyfor this table.
pass_from is a foreignkeyto player_id from Teams table.
Eachrow represents a pass made during a match, time_stamp represents the time in minutes (00:00-90:00) when the pass was made,
pass_to is the player_id of the player receiving the pass.
Write a solution to calculate the dominance score for each team in both halves of the match. The rules are as follows:
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
A higher dominance score indicates better passing performance
Return the result table orderedbyteam_name and half_numberin ascending order.
Input:
Teams table:+------------+-----------+| player_id | team_name |+------------+-----------+|1| Arsenal ||2| Arsenal ||3| Arsenal ||4| Chelsea ||5| Chelsea ||6| Chelsea |+------------+-----------+Passes table:+-----------+------------+---------+| 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|+-----------+-------------+-----------+Explanation:
-**First Half(00:00-45:00):**- Arsenal's passes:-1→2(00:15): Successful pass(+1)-2→3(00:45): Successful pass(+1)-3→1(01:15): Successful pass(+1)- Chelsea's passes:-4→1(00:30): Intercepted by Arsenal(-1)-**Second Half(45:01-90:00):**- Arsenal's passes:-2→3(46:00): Successful pass(+1)-3→4(46:15): Intercepted by Chelsea(-1)-1→2(46:45): Successful pass(+1)- Chelsea's passes:-5→6(46:30): Successful pass(+1)- The results are ordered by team_name and then half_number
For each pass we can attribute +1 to the passer’s team when the receiver is on the same team and -1 when the receiver belongs to the opponent. We also need to split passes into two halves by parsing the minute and second from the timestamp. Summing these +1/−1 values per team and per half gives the dominance score.
Join Passes with Teams twice to get the passer’s team (team_from) and the receiver’s team (team_to).
Parse time_stamp into minutes and seconds and classify each pass into half_number = 1 (first half) or 2 (second half). First half includes all timestamps <= 45:00, second half is strictly after 45:00.
For each pass row compute score = CASE WHEN team_from = team_to THEN 1 ELSE -1 END.
Aggregate SUM(score) grouped by team_name and half_number and order the result by team_name, half_number ascending.
SELECT s.team_name,
s.half_number,
SUM(s.score) AS dominance
FROM (
SELECT tf.team_name AS team_name,
CASEWHENCAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) <45THEN1WHENCAST(SUBSTRING_INDEX(p.time_stamp, ':', 1) AS UNSIGNED) =45ANDCAST(SUBSTRING_INDEX(p.time_stamp, ':', -1) AS UNSIGNED) =0THEN1ELSE2ENDAS half_number,
CASEWHEN tf.team_name = tt.team_name THEN1ELSE-1ENDAS score
FROM Passes p
JOIN Teams tf ON p.pass_from = tf.player_id
JOIN Teams tt ON p.pass_to = tt.player_id
) s
GROUPBY s.team_name, s.half_number
ORDERBY s.team_name ASC, s.half_number ASC;