SQL Exercise: Find the players who booked most number of times
42. From the following tables, write a SQL query to count the players who booked the most number of times. Return player name, number of players who booked most number of times.
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.........
1229 | NOR | Norway
Sample table: player_booked
match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
1 | 1216 | 160349 | 32 | | NT | 1
1 | 1216 | 160355 | 45 | | NT | 1
1 | 1207 | 160159 | 69 | Y | NT | 2
1 | 1216 | 160360 | 78 | | NT | 2
2 | 1221 | 160470 | 14 | | NT | 1
2 | 1201 | 160013 | 23 | | NT | 1
2 | 1201 | 160013 | 36 | | NT | 1
2 | 1201 | 160014 | 63 | | NT | 2
2 | 1221 | 160472 | 66 | | NT | 2
........
51 | 1214 | 160302 | 122 | | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample Solution:
SQL Code:
-- This query selects the player name and the count of bookings for each player from the player_booked table, grouped by player name.
SELECT
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(b.*) as Booked -- Counting the number of bookings for each player
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_booked b ON a.country_id = b.team_id -- Joining the soccer_country table with the player_booked table based on the country_id
JOIN
player_mast c ON b.player_id = c.player_id -- Joining the player_mast table with the player_booked table based on the player_id
GROUP BY
c.player_name -- Grouping the results by player name
HAVING
COUNT(b.*) = ( -- Applying a filter to only include rows where the count of bookings for a player is equal to
SELECT MAX(mm) -- the maximum count of bookings among all players
FROM (
SELECT COUNT(*) mm -- Subquery to count the number of bookings for each player and alias it as 'mm'
FROM player_booked
GROUP BY player_id
) inner_result -- Alias for the subquery result
);
Sample Output:
player_name | booked
-------------------+--------
NGolo Kante | 3
William Carvalho | 3
Bartosz Kapustka | 3
(3 rows)
Code Explanation:
The said query in SQL that joins three tables soccer_country, player_booked, and player_mast and groups the results by player name.
The JOIN clause joins the soccer_country and player_booked tables based on the country_id and team_id columns, and joins the result set with the table player_mast based on the player_id column.
Groups the results by player_name, so that the query can aggregate bookings by player.
Using a subquery finds the player with the highest number of bookings, and returns only that player's name and the number of bookings they have. The subquery first counts the number of bookings per player in the player_booked table, groups the result by player_id, and selects the maximum count. The outer query then filters the results to only include the player with that maximum count.
Alternative Solution:
Using a Correlated Subquery:
-- Selecting the player name and the count of bookings for each player from the player_booked table,
-- grouped by player name, using a subquery to determine players with the maximum bookings.
SELECT
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(b.*) as Booked -- Counting the number of bookings for each player
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_booked b ON a.country_id = b.team_id -- Joining the soccer_country table with the player_booked table based on the country_id
JOIN
player_mast c ON b.player_id = c.player_id -- Joining the player_mast table with the player_booked table based on the player_id
JOIN
( -- Subquery to calculate the total bookings for each player and alias it as 'sub'
SELECT player_id, COUNT(*) as total_bookings
FROM player_booked
GROUP BY player_id
) as sub ON c.player_id = sub.player_id -- Joining the main query with the subquery based on the player_id
GROUP BY
c.player_name -- Grouping the results by player name
HAVING
COUNT(b.*) = ( -- Applying a filter to only include rows where the count of bookings for a player is equal to
SELECT MAX(total_bookings) -- the maximum count of bookings among all players
FROM ( -- Subquery to calculate the maximum total bookings among all players
SELECT COUNT(*) as total_bookings
FROM player_booked
GROUP BY player_id
) as inner_result -- Alias for the subquery result
);
Explanation:
This query uses a correlated subquery in the HAVING clause to find the maximum count of bookings for each player. It counts the bookings for each player and selects players with the maximum count.
Go to:
PREV : Players with their teams booked in the tournament.
NEXT : Find the number of players booked for each team.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.