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.

This exercise is part of the course

Reporting in SQL

View Course

Exercise 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample 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 ____;

Edit and Run Code