Exercise

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.

Instructions

100 XP
  • 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.