One Pager Cheat Sheet

• A Common Table Expression (CTE) acts as a virtual table generated by a WITH clause that can be used for query simplication and data conversion.
• Yes, CTEs are created at runtime and `last only for the duration of the query itself.
• CTEs improve the readability and simplicity of complicated queries by breaking them down into basic building parts that can be reused, solving use cases such as using derived tables multiple times, performing the same computation multiple times, and substituting views.
• There exist two distinct types of CTEs: recursive and non-recursive; the former is useful for working with hierarchical data whereas the latter are simple and do not include any repeated processing.
• Using a Common Table Expression (CTE) we can group and join data from Table1 to retrieve a table with a calculated average age for each item number of year.
• The CTE number_orders is used to calculate the average number of orders per customer with the result being the avg_order_count.