1. Filtering and finishing touches
At this point, we have the bulk of our query finalized. There are only a few more items to add, starting with filters.
2. Goal report
Here's our goal report. The subtitle clearly states that only Western European Countries should be included, which means we need to add a filter.
3. Filtering
The E:R diagram states we need to filter by the region field, which is found in the table countries. However, our query does not currently reference this table. We will need to add it either through a JOIN or a subquery. Let's start with the subquery approach.
4. Filtering with a subquery
Here's our query, or rather, the top half of it. Remember that our query has two halves, so if we make an update to the top half, which references summer_games, we can mirror the logic to the bottom half for updates to winter_games.
5. Filtering with a subquery
Before adding the where statement, let's build our subquery. We want to filter for Western European countries, so the output of our subquery should show a list of countries or country_ids. We can pull a list of ids from the countries table, filtered for the region 'WESTERN EUROPE'. This outputs a list of relevant ids.
6. Filtering with a subquery
Now that we have the subquery setup, let’s add our WHERE statement in the correct place. We want to filter by country_ids, so the WHERE statement should show WHERE country id IN some list.
7. Filtering with a subquery
We can replace the list with our subquery. All we have to do now is replicate the logic on the bottom half of the query, which isn't shown here. So that's how filtering with the subquery approach works.
8. Filtering with a JOIN
As mentioned, we could have filtered using a join, shown here. Notice all we added was a JOIN statement and a WHERE statement using this approach.
9. Remaining questions
There are only 2 remaining questions. Do we need an ORDER BY or a LIMIT? In our example, there doesn't appear to be any limiting, but we do need to order by golds in descending order.
10. Final code
So here's the final code. Again, due to sheer space on the slide, I had to add three dots to the bottom portion of the query, but the bottom half should have the same lines as the top half. Notice how the order statement only appears at the very end of the script, indicating limiting and ordering happens AFTER the union. The full script has 36 lines of code.
11. Order of operations
For full understanding, let’s conceptually go over the steps SQL takes when running our query. First, we created two queries, each that JOINs a games table to the athletes table.
12. Order of operations
Next, we added custom logic to each query, such as the case and where statements.
13. Order of operations
Then we UNIONed the two together.
14. Order of operations
And finally ordered the results. That is the order SQL takes when constructing this report. Do note, though, that this approach requires us to duplicate code quite a bit.
15. Option B
Remember how we mentioned there are two options of combining our tables? Well, here's what the code would look like with the second option. It's shorter overall, since we do not have to duplicate our logic, but could be a bit confusing with an extra subquery tucked away. If you're curious how it works, feel free to pause the video and look into it.
16. Capstone exercise
Let's look back at your task, which is to create this visualization. You will take a similar approach to what we just did to build this report, taking it one step at a time.
17. Let's practice!
So let's get to it!