SQL Exercise: Find the match where highest number of penalty taken

SQL soccer Database: Subqueries Exercise-27 with Solution

27. From the following table, write a SQL query to find the matches with the most penalty shots.

 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 match_no,
       COUNT(*) shots
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);

Sample Output:

 match_no | shots
----------+-------
       47 |    18
(1 row)

Code Explanation:

The said query in SQL that selects the match number and the number of shots taken in a penalty shootout for those matches, where the number of shots taken is equal to the maximum number of shots taken in any match.
The match_no and COUNT(*), which counts the number of rows in the penalty_shootout table for each group of match_no values.
The GROUP BY clause groups the rows in the penalty_shootout table by match_no.
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 match_no, shots
FROM (
    SELECT match_no, COUNT(*) AS shots,
           MAX(COUNT(*)) OVER () AS max_shots
    FROM penalty_shootout
    GROUP BY match_no
) AS shots_counts
WHERE shots = max_shots;

Explanation:

This query uses a window function to calculate both the shots count for each match and the maximum shots count. It then selects the matches where the shots count equals the maximum shots count.

Using Subqueries with Aggregation and JOIN:

SELECT a.match_no, COUNT(*) AS shots
FROM penalty_shootout a
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
        ) AS inner_result
    )
) b ON a.match_no = b.match_no
GROUP BY a.match_no;

Explanation:

This query uses subqueries with aggregation and joins to first calculate the shots count for each match. It then applies a HAVING clause to filter the matches with the maximum shots and uses GROUP BY to count the shots for each match.

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 maximum number of penalty shots taken.
Next SQL Exercise: Match highest number of penalty shots had been taken.