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
orwinter
- 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
Exercise instructions
- In the subquery, construct a query that outputs
season
,country_id
andevent
by combining summer and winter games with aUNION ALL
. - Leverage a
JOIN
and anotherSELECT
statement to show the fieldsseason
,country
and uniqueevents
. 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 ____;