What is a CTE?
A common table expression or CTE is essentially a named subquery that can be called repeatedly without having to replicate code as you would with subqueries. This improves readability and allows you to easily reuse logic within a script. Additionally, by placing semicolons before and after a CTE you can edit the logic and test it to confirm it works as you expect whereas subqueries require all logic throughout the query to work properly - making it harder to debug.
How to build a CTE:
Like a subquery, it is good practice (unless you have a lot of SQL experience) to build out your CTE below your main script first. This will help check that it works as intended and allow for easier implementation within the main query later on. In this example we will construct a CTE to calculate the average sales per region which will be shown against each customer and their total sales for comparison.
The script below extracts customers, region and total sales from the corresponding tables within the schema:
We almost have everything we need to reach the result we want but we need to calculate the average sales by region. Now you may think, similarly to the "Total Sales" why haven't I added "AVG(o.SALES)"? This hasn't been done as it would return the average sales by both region and customer:
In order to calculate the sales by the granularity we want, we must construct our CTE. Below the clause, we will construct a query to return average sales by region from the respective tables:
It's a simple query for the purposes of this example but more complex logic, joins and aggregations can be used in CTE's. We can run this query and see it works as expected:
To construct the CTE now, we will copy and paste this new script above the original main script. For a CTE to be recognised as one by Snowflake we will encase this script within brackets. Finally we will name the CTE before the opening bracket using the syntax: "WITH 'CTE name you pick' AS" as you can see here:
I added an alias for the regional average sales field so as to keep each field clear when joining it back up with the main script and I've named this CTE regional_sales. If you have multiple CTE's simply add a comma after the closing bracket, "CTE name for the next CTE" and AS then have the query again encased in brackets:
Finally for our CTE to function we must now add in a join clause to retrieve the field from it within the main script:
Here we can see our CTE expression above the main script, we can see the AVG region sales being selected and the key clause required for the CTE to work in the main script is the join clause on customers table by region. The last line of the script displays the average regional sales in the output with the first 3 fields to give us the result we wanted:
Now we have our desired result and if down the line we needed to reuse the same logic to get average regional field it would be as simple as selecting it like a field from a table.
This was a simple walkthrough of CTE's, how to construct & use them and their purpose. This will require a lot of practise but is a handy feature to use especially in longer, more complex scripts.