SQL Exercise: Countries where assistant referees assist most matches
47. From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches.
Sample table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
.........
51 | F | 1207 | L | N | 0 | | 80007 | 160140
Sample table: asst_referee_mast
ass_ref_id | ass_ref_name | country_id
------------+--------------------------+------------
80034 | Tomas Mokrusch | 1205
80038 | Martin Wilczek | 1205
80004 | Simon Beck | 1206
80006 | Stephen Child | 1206
80007 | Jake Collin | 1206
80014 | Mike Mullarkey | 1206
80026 | Frederic Cano | 1207
80028 | Nicolas Danos | 1207
80005 | Mark Borsch | 1208
........
80015 | Tarik Ongun | 1222
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 Solution:
SQL Code:
-- Selecting the country name and the count of distinct match numbers for each country where an assistant referee is involved
SELECT
country_name, -- Selecting the country name from the soccer_country table
count(DISTINCT match_no) -- Counting the distinct match numbers for each country
FROM
match_details a -- Specifying the match_details table with alias 'a'
JOIN
asst_referee_mast c ON a.ass_ref = c.ass_ref_id -- Joining the match_details table with the asst_referee_mast table on the assistant referee ID
JOIN
soccer_country b ON c.country_id = b.country_id -- Joining the asst_referee_mast table with the soccer_country table on the country ID
GROUP BY
country_name -- Grouping the results by country name
HAVING
count(DISTINCT match_no) = ( -- Filtering the results to only include rows where the count of distinct match numbers is equal to...
SELECT
max(mm) -- Selecting the maximum count of distinct match numbers
FROM
( -- Subquery to calculate the count of distinct match numbers for each country and find the maximum count
SELECT
count(DISTINCT match_no) mm -- Counting the distinct match numbers for each country and assigning the result to the alias 'mm'
FROM
match_details a -- Specifying the match_details table with alias 'a'
JOIN
asst_referee_mast c ON a.ass_ref = c.ass_ref_id -- Joining the match_details table with the asst_referee_mast table on the assistant referee ID
JOIN
soccer_country b ON c.country_id = b.country_id -- Joining the asst_referee_mast table with the soccer_country table on the country ID
GROUP BY
country_name -- Grouping the results by country name
) hh -- Assigning the subquery results to the alias 'hh'
);
Sample Output:
country_name | count --------------+------- England | 7 (1 row)
Code Explanation:
The said query in SQL that selects the name of countries and the count of distinct match numbers in the match_details table for each country.
The HAVING clause filters the results to only show the countries whose count of distinct match numbers is equal to the maximum count of distinct match numbers among all countries. The maximum count of distinct match numbers is obtained by a subquery that selects the count of distinct match numbers for each country and then selects the maximum count from those results.
The JOIN clause joins the match_details and asst_referee_mast table based on the ass_ref and ass_ref_id columns, joins the results with the soccer_country tables based on the country_id column to obtain the required data.
Alternative Solution:
Cross Join with Subquery and HAVING:
-- Selecting the country name and the count of distinct match numbers for each country where an assistant referee is involved
SELECT
b.country_name, -- Selecting the country name from the soccer_country table
COUNT(DISTINCT a.match_no) AS Match_Count -- Counting the distinct match numbers for each country and assigning the result to the alias 'Match_Count'
FROM
soccer_country b -- Specifying the soccer_country table with alias 'b'
CROSS JOIN
asst_referee_mast c -- Performing a cross join with the asst_referee_mast table with alias 'c'
LEFT JOIN
match_details a ON a.ass_ref = c.ass_ref_id AND c.country_id = b.country_id -- Left joining the match_details table with the cross join of soccer_country and asst_referee_mast tables on the assistant referee ID and country ID
GROUP BY
b.country_name -- Grouping the results by country name
HAVING
COUNT(DISTINCT a.match_no) = ( -- Filtering the results to only include rows where the count of distinct match numbers is equal to...
SELECT
MAX(cnt) -- Selecting the maximum count of distinct match numbers
FROM
( -- Subquery to calculate the count of distinct match numbers for each country and find the maximum count
SELECT
COUNT(DISTINCT match_no) AS cnt -- Counting the distinct match numbers for each country and assigning the result to the alias 'cnt'
FROM
match_details x -- Specifying the match_details table with alias 'x'
JOIN
asst_referee_mast y ON x.ass_ref = y.ass_ref_id -- Joining the match_details table with the asst_referee_mast table on the assistant referee ID
JOIN
soccer_country z ON y.country_id = z.country_id -- Joining the asst_referee_mast table with the soccer_country table on the country ID
GROUP BY
z.country_name -- Grouping the results by country name
) sub -- Assigning the subquery results to the alias 'sub'
);
Explanation:
This query uses a cross join to generate all possible combinations of country names and assistant referees. It then left joins with match details to count the distinct matches. The HAVING clause filters the results based on the match count being equal to the maximum count.
Go to:
PREV : Assistant referees of each countries assists matches.
NEXT : List the referees with their countries for each match.
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.