SQL Exercise: Match highest number of penalty shots had been taken

SQL soccer Database: Subqueries Exercise-28 with Solution

From the following table, write a SQL query to determine the match number where the most penalty shots were taken. Return match number, country name.

 kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
       1 |       37 |    1221 |    160467 | Y          |       1
       2 |       37 |    1213 |    160297 | Y          |       2
       3 |       37 |    1221 |    160477 | N          |       3
       4 |       37 |    1213 |    160298 | Y          |       4
       5 |       37 |    1221 |    160476 | Y          |       5
       6 |       37 |    1213 |    160281 | Y          |       6
       7 |       37 |    1221 |    160470 | Y          |       7
       8 |       37 |    1213 |    160287 | Y          |       8
       9 |       37 |    1221 |    160469 | Y          |       9
      10 |       37 |    1213 |    160291 | Y          |      10
      11 |       45 |    1214 |    160322 | Y          |       1
      12 |       45 |    1213 |    160297 | Y          |       2
      13 |       45 |    1214 |    160316 | Y          |       3
      14 |       45 |    1213 |    160298 | Y          |       4
      15 |       45 |    1214 |    160314 | Y          |       5
      16 |       45 |    1213 |    160281 | Y          |       6
      17 |       45 |    1214 |    160320 | Y          |       7
      18 |       45 |    1213 |    160287 | N          |       8
      19 |       45 |    1214 |    160321 | Y          |       9
      20 |       47 |    1211 |    160251 | Y          |       1
      21 |       47 |    1208 |    160176 | Y          |       2
      22 |       47 |    1211 |    160253 | N          |       3
      23 |       47 |    1208 |    160183 | N          |       4
      24 |       47 |    1211 |    160234 | Y          |       5
      25 |       47 |    1208 |    160177 | N          |       6
      26 |       47 |    1211 |    160252 | N          |       7
      27 |       47 |    1208 |    160173 | Y          |       8
      28 |       47 |    1211 |    160235 | N          |       9
      29 |       47 |    1208 |    160180 | N          |      10
      30 |       47 |    1211 |    160244 | Y          |      11
      31 |       47 |    1208 |    160168 | Y          |      12
      32 |       47 |    1211 |    160246 | Y          |      13
      33 |       47 |    1208 |    160169 | Y          |      14
      34 |       47 |    1211 |    160238 | Y          |      15
      35 |       47 |    1208 |    160165 | Y          |      16
      36 |       47 |    1211 |    160237 | N          |      17
      37 |       47 |    1208 |    160166 | Y          |      18

Sample Solution:

SQL Code:

SELECT b.match_no,
       a.country_name
FROM penalty_shootout b,
     soccer_country a
WHERE b.team_id=a.country_id
  AND match_no=
    (SELECT match_no
     FROM penalty_shootout
     GROUP BY match_no
     HAVING COUNT(*)=
       (SELECT MAX(shots)
        FROM
          (SELECT COUNT(*) shots
           FROM penalty_shootout
           GROUP BY match_no) inner_result))
GROUP BY b.match_no,
         a.country_name;

Sample Output:

 match_no | country_name
----------+--------------
       47 | Germany
       47 | Italy
(2 rows)

Code Explanation:

The said query in SQL that joins the penalty_shootout and soccer_country tables to return the country names of the teams that participated in the match with the maximum number of shots taken in a penalty shootout.
The WHERE clause filters the rows from the two tables to only include those where the team_id column in penalty_shootout matches the country_id column in soccer_country, and the match_no column in penalty_shootout matches the result of the subquery that is the GROUP BY clause groups the rows by both match_no and country_name.
The subquery in the HAVING clause finds the maximum number of shots taken in any match by first grouping the rows in the penalty_shootout table by match_no, counting the number of rows in each group, and then selecting the maximum count from those groups.

Alternative Solution:

Using a Window Function:

SELECT b.match_no, a.country_name
FROM penalty_shootout b
JOIN soccer_country a ON b.team_id = a.country_id
WHERE b.match_no = (
    SELECT match_no
    FROM (
        SELECT match_no, RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
        FROM penalty_shootout
        GROUP BY match_no
    ) ranked_matches
    WHERE rnk = 1
)
GROUP BY b.match_no, a.country_name;

Explanation:

This query uses a window function (RANK()) to rank matches based on the shots count. It then selects the match with the highest rank (i.e., the match with the maximum shots count).

Using Subqueries with JOIN:

SELECT b.match_no, a.country_name
FROM penalty_shootout b
JOIN soccer_country a ON b.team_id = a.country_id
JOIN (
    SELECT match_no, COUNT(*) AS shots
    FROM penalty_shootout
    GROUP BY match_no
    HAVING COUNT(*) = (
        SELECT MAX(shots)
        FROM (
            SELECT COUNT(*) AS shots
            FROM penalty_shootout
            GROUP BY match_no
        ) inner_result
    )
) max_shots ON b.match_no = max_shots.match_no
GROUP BY b.match_no, a.country_name;

Explanation:

This query uses subqueries with JOIN to first find the match with the maximum shots count. It then joins the penalty_shootout and soccer_country tables, filtering for matches with the maximum shots count.

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the match where highest number of penalty taken.
Next SQL Exercise: Player of portugal taken the 7th kick against poland.