Multiple common table expressions
1. Multiple common table expressions
Calling all common table expression-lovers; it's time to build queries with multiple CTEs!2. Common table expressions (CTEs)
Common table expressions, or CTE's, are a tool that Snowflake provides to temporarily store the results of a query that will eventually be used within another query. CTE's are defined using the `WITH` keyword, followed by the name that temporary results can be accessed via, `AS`, and a `SELECT` statement. CTE's are used to help organize queries, with the goal of making them more readable and modular. In this example, only one CTE is defined. But, believe it or not, we're not just limited to one CTE.3. Defining multiple CTEs
In fact, multiple temporary results can be defined using a single `WITH` statement. To do this, a comma, followed by the name of second CTE, AS, and a `SELECT` statement in parentheses is all that's needed. Defining multiple CTE's makes things like table filtering and manipulation easier to understand. It also allows us to build some pretty neat queries, by joining multiple CTE's together, or subquerying within another CTE.4. Joining temporary result sets
Here, we've defined two CTE's; `seniors`, which retrieves personal information for all students with a graduation year of 2025, and `final_exam_grades`. Once `final_exam_grades` is defined, these two CTE's can be joined together, on the `student_id` field. Writing two CTE's with their filtering logic isolated makes each component easy to understand and change if needed.5. Joining temporary result sets
The final output looks like this, a report-ready dataset.6. Using a temporary result in a CTE
Here's something pretty cool; we can use the results of a CTE in another! That's right! Here, `ny_schools` stores a temporary result set containing the `school_id`, `school_name`, and `school_district` from all schools in New York state. When defining `ny_teachers`, the result is filtered by `school_id` using a subquery. `ny_teachers` will only contain records where the `school_id` is in the `ny_schools` CTE.7. Using a temporary result in a CTE
Let's dig a bit deeper. After we've filtered for `school_id`'s in New York and stored the results in `ny_school`, we can use `ny_schools` in the `ny_teachers` CTE. In a `WHERE` clause`, only records whose `school_id` is in the `school_id` column of `ny_schools` is returned. We can do just about anything we'd like in `ny_teachers` once `ny_schools` has been defined, such as a `JOIN` within `ny_teachers` itself.8. Matching teachers workloads to their specialties
Finally, we'll put it all together. After we've built the `ny_teachers` CTE by filtering for `school_id`'s in New York, we can join `ny_teachers` to the `previous_courses` table.9. Matching teachers workloads to their specialties
The results look like this! We've successfully built an output that shows all courses and terms for teachers who taught within their specialty.10. Let's practice!
It's your turn to build queries with multiple common table expressions! Happy practicing!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.