1. Creating custom fields
In this lesson, we will add another layer to our query by building custom fields.
2. Goal report
Here's a preview of the report we are trying to build, as well as by the current state of our query. So far, we've combined the tables through JOINs and UNIONs but kept placeholder fields in for now. Let's update the fields to correctly match the columns on the report.
3. Preparation
Since we have two sides of our query separated by a UNION, it is easier to work with one side at a time. As a preparation step, comment out the second half of the query so we can just focus on the top half. We can update the bottom after.
4. Preparation
Next, replace the fields with placeholders for our fields. Since we don't know how the fields should be setup yet, add blanks before each field and comment out each line with dashes. This setup allows us to efficiently test our code after each step.
5. Field 1: seasons
Let's start with the season field. We want to output 'Summer' for all summer games, which applies to the entire top half of the query. We can add a static field called season here, by just adding the string in single quotes. Simple as that!
6. Field 2: golds
Next, let's setup the golds field. For this, we just need a basic SUM function. Let's uncomment the field and test it out. Looks good!
7. Field 3: demographic_group
Now for the fun one: demographic_group. This requires us to bucket athletes based on their AGE and GENDER. To setup this type of conditional, we can use a CASE statement.
8. CASE statement
Here is a quick refresher on CASE statement syntax. In a case statement, we define a conditional and indicate what the output should be when that condition is satisfied. If no condition is satisfied, we output the ELSE statement. There's no limit on the number of allowed conditions.
9. Field 3: demographic_group
We want four different outputs for demographic_group, so I've updated the CASE statement to show all four outputs. Now, all we need to do is update the conditionals for each.
10. Field 3: demographic_group
An athlete that is male, age 13-25, must satisfy three separate conditions. Age should be at least 13, age should be no more than 25, and gender should be male. We can combine all three using AND statements, as shown.
11. Field 3: demographic_group
We can take similar approach for the other three conditionals. Here's the full case statement. Let's run this to make sure it works. Oh no! We get an error! It looks like we need to add a group by clause. This is because we introduced a dimension into a query that already contained an aggregation.
12. Field 3: demographic_group
So let's tell SQL to group by the new dimension. It works!
13. Field 3: demographic_group
Also, notice how we have no ELSE statement. This makes it so if no condition is satisfied, the report will output a "null". With this setup, if we see a "null", it's clear there is an issue that requires troubleshooting. If we had an "else" statement, we wouldn't be able to see any nulls, which means errors would not be easily identifiable.
14. New state of query
So that's all three fields! Remember, that was only for half the query, so let's replicate this code for the bottom half, making sure we update summer to winter. I added three dots near the bottom of our query to fit the slide, but note that it should show the same CASE statement as above.
15. Let's practice!
We're getting close! Before we move on to the final steps, let's practice creating some custom fields.