SQL Exercise: 2nd highest stoppage time in the 2nd half of matches
23. From the following tables, write a SQL query to find those match(s) where the second highest amount of stoppage time was added in the second half of the match. Return match number, country name and stoppage time.
Sample table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
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: 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:
-- This query selects specific columns from the match_mast, match_details, and soccer_country tables.
SELECT
a.match_no, -- Selecting the match number from the match_mast table aliased as 'a'
c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
a.stop2_sec -- Selecting the stoppage time from the match_mast table aliased as 'a'
FROM
match_mast a -- Specifying the match_mast table with an alias 'a'
JOIN
match_details b ON a.match_no = b.match_no -- Joining the match_details table with the match_mast table based on the match number
JOIN
soccer_country c ON b.team_id = c.country_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE
(2 - 1) = ( -- Checking if the difference between 2 and 1 is equal to
SELECT
COUNT(DISTINCT(b.stop2_sec)) -- Counting the distinct stoppage times from the match_mast table aliased as 'b'
FROM
match_mast b -- Specifying the match_mast table for the subquery aliased as 'b'
WHERE
b.stop2_sec > a.stop2_sec -- Filtering stoppage times greater than the stoppage time in the main query
);
Sample Output:
match_no | country_name | stop2_sec
----------+--------------+-----------
15 | France | 374
15 | Albania | 374
(2 rows)
Code Explanation:
The said query in SQL that retrieves data from the tables match_mast, match_details, and soccer_country. It selects the match number, country name, and stop2_sec columns from the respective tables.
The JOIN clause joins the table match_mast on match_no with match_details, and match_details on team_id with soccer_country.
Filters the data based on the condition that the difference between the number 2 and the number 1 is equal to the count of distinct stop2_sec values in match_mast table that are greater than the stop2_sec value in the current row of match_mast table.
Alternative Solutions:
Using a Self-Join:
-- This query selects specific columns from the match_mast, match_details, and soccer_country tables.
SELECT
a.match_no, -- Selecting the match number from the match_mast table aliased as 'a'
c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
a.stop2_sec -- Selecting the stoppage time from the match_mast table aliased as 'a'
FROM
match_mast a -- Specifying the match_mast table with an alias 'a'
JOIN
match_details b ON a.match_no = b.match_no -- Joining the match_details table with the match_mast table based on the match number
JOIN
soccer_country c ON b.team_id = c.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN
match_mast d ON a.stop2_sec < d.stop2_sec -- Joining the match_mast table with itself to find matches with stoppage time greater than the current match
GROUP BY
a.match_no, c.country_name, a.stop2_sec -- Grouping the results by match number, country name, and stoppage time
HAVING
-- Filtering the groups where there's exactly one distinct stoppage time greater than the current match's stoppage time
COUNT(DISTINCT d.stop2_sec) = 1;
Explanation:
This query uses a self-join on match_mast (alias d) with a condition that compares stop2_sec. It then groups the results by match_no, country_name, and stop2_sec. The HAVING clause ensures that only matches with a single unique stop2_sec value are selected.
Using a JOIN with Subquery:
-- This query selects specific columns from the match_mast, match_details, and soccer_country tables.
SELECT
a.match_no, -- Selecting the match number from the match_mast table aliased as 'a'
c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
a.stop2_sec -- Selecting the stoppage time from the match_mast table aliased as 'a'
FROM
match_mast a -- Specifying the match_mast table with an alias 'a'
JOIN
match_details b ON a.match_no = b.match_no -- Joining the match_details table with the match_mast table based on the match number
JOIN
soccer_country c ON b.team_id = c.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN
(
-- Subquery to select distinct stoppage times
SELECT DISTINCT
stop2_sec -- Selecting distinct stoppage times
FROM
match_mast
) d ON a.stop2_sec < d.stop2_sec -- Joining the distinct stoppage times with the match_mast table to find matches with stoppage time greater than the current match
GROUP BY
a.match_no, c.country_name, a.stop2_sec -- Grouping the results by match number, country name, and stoppage time
HAVING
-- Filtering the groups where there's exactly one record, indicating that the stoppage time is unique
COUNT(*) = 1;
Explanation:
This query uses a subquery (d) to get a distinct list of stop2_sec values. It then joins this subquery with the main tables and applies the condition b.stop2_sec < d.stop2_sec. The result is grouped and only matches with a count of 1 are selected.
Go to:
PREV : Matches end in a goalless draw in group stage of play.
NEXT : How many games the goalkeeper played for his 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.