1. Applying conditional logic in Snowflake
It's time to apply what you've learned and build more advanced `CASE` statements!
2. Aggregation with CASE statements
Believe it or not, `CASE` statements can be used in tandem with aggregation functions. These include the likes of `SUM`, `AVG`, `MIN`, and `MAX`. With Snowflake, you'll have additional functions at your disposal, like `MODE`.
Query structure will look nearly identical, but will now include one or more of these functions.
You might operate on the output of a `CASE` statement itself; we'll take a look at that in a bit.
Like with any query using an aggregation function, you'll need to `GROUP BY` the fields that aren't being aggregated.
3. Aggregation with CASE statements
Let's say that we want to find the average temperature for each season of a particular year. Using a `CASE` statement, we can do this!
Here, we've created a `CASE` statement to classify each month as being either "Winter", "Spring", "Summer", or "Fall". Then, the `AVG` function is used to find that season's average temperature.
Finally, we add a `GROUP BY` statement to roll up our results by season and `year_num`.
4. Aggregation with CASE statements
Each record was assigned a season based on it's date. Then, the `AVG` function was used to find the average temperature for each season and year.
5. Aggregating CASE'd fields
Check out this query! We're using `AVG` on the output of a `CASE` statement itself to compare temperature to the typical average for a season.
Don't forget to `GROUP BY` `season`.
6. Aggregating CASE'd fields
Here's the output, with the freshly minted `relation_to_average_temperature` field.
7. JOIN's
We can also `JOIN` data when writing `CASE` statements. This is particularly useful when we'd like to evaluate data from more than one table. Our query structure won't change much. But now, we'll have a `JOIN` statement after `FROM`.
You can use any `JOIN` type that you'd like, such as `LEFT`, `RIGHT`, `INNER`, or `OUTER`. The best part is, we can still use other tools we picked up earlier, such as aggregation functions.
Since Snowflake is a columnar database, these operations are extremely performant, and won't slow your queries down.
Let's see one in action!
8. Determining college credit status
We've broken this query up into three parts. Let's start at the bottom. Here, we're `LEFT JOIN`ing the `student_courses` and `student`s table on the `student_id` field.
We can now use columns from either table in the rest of the query. In the `CASE` statement, we're using both the `students.grade_num` and `student_courses.grade` columns to determine `college_credit_status`.
9. Determining college credit status
Each course that a student takes will be assigned a `college_credit_status`. This information is quite powerful; a view like this might be included in a report or dashboard for students and teachers alike.
10. Let's practice!
Now, it's your turn to flex your Snowflake muscles with a couple more exercises.