UNION then JOIN query

Your goal is to create the same report as before, which contains with the following fields:

season, which outputs either summer or winter
country
events, which shows the unique number of events

In this exercise, create the query by using the UNION first, JOIN second approach. When taking this approach, you will need to use the initial UNION query as a subquery. The subquery will need to include all relevant fields, including those used in a join.

As always, feel free to reference the E:R Diagram.

Cet exercice fait partie du cours

Reporting in SQL

Afficher le cours

Instructions

In the subquery, construct a query that outputs season, country_id and event by combining summer and winter games with a UNION ALL.
Leverage a JOIN and another SELECT statement to show the fields season, country and unique events.
GROUP BY any unaggregated fields.
Sort the report by events in descending order.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

-- Add outer layer to pull season, country and unique events
SELECT 
	____, 
    ____, 
    ____ AS events
FROM
    -- Pull season, country_id, and event for both seasons
    (SELECT 
     	____ AS season, 
     	____, 
     	____
    FROM ____
    ____
    SELECT 
     	____ AS season, 
     	____, 
     	____
    FROM ____) AS subquery
JOIN ____ AS c
ON ____
-- Group by any unaggregated fields
GROUP BY ____
-- Order to show most events at the top
ORDER BY ____;

Modifier et exécuter le code