1. Common Table Expressions
If you think subqueries are useful, wait until you see what we have in store with common table expressions!
2. Common Table Expressions
Common table expressions, or CTEs, are one of the most useful tools for any Snowflake practitioner. CTEs temporarily store the result of a query so it can eventually be used by another query.
A CTE is defined at the start of a query using the WITH keyword, followed by a name we choose, then AS, and a query in parentheses. The result set of that query is temporary stored using that name, and can be treated like a table.
CTEs are similar to subqueries and can be used in the same way; we’ll explore the differences soon.
3. Reporting on at-risk students
Here's an example of a common table expression used to identify students at-risk of failing a course. First, we define a CTE called `at_risk`. This retrieves a subset of records from `student_courses` where a student's grade is below 70 for a required course. Remember, the syntax is "with-cte-name-as-query".
Once we've temporarily stored the results in `at_risk`, we can `JOIN` the CTE to the `students` table to generate a report.
4. Reporting on at-risk students
Check out the results!
This output should help when visualizing the organization of a CTE. First, we created a temporary result set; that's the data on top. Then, outside of the CTE, we joined this result set to the `students` table to generate the final report.
5. Comparing Subqueries and CTE's
So, what's the difference between a subquery and a common table expression. Let's answer that question with an example.
On the left is a query that we've already seen; it finds both the average and greatest temperature differential for each month using a subquery.
We can do the same thing using a CTE. On the right, we can see the same logic, now implemented using a CTE. Both techniques allow us to use the result set of another query to make the logic more readable and modular.
However, the organization of a CTE is bit more intuitive than a subquery. We're defining the temporary result set before we use it; this is quite familiar for programmers. We could even re-use `daily_temperature_differential` if we'd like.
6. Finding temperature differential
Like before, we can visualize stepping through a CTE. `daily_temperature_differential` generates a temporary result set that includes the `month_num` and `differential`. Once this is defined, it can be used to generate the final result.
This progression is a bit more natural than using a subquery, and is one of the reasons why common table expressions are so popular.
7. Let's practice!
Now, it's your turn; time to practice!