Earlier in the tutorial, you learned that the select function could only be used once in a query. This is actually not entirely true — but you need to be careful about how you structure a query to make sure that it returns the desired result. This is how a CTE query is structured:
The first step of the query groups by 'order_id', and the second step aggregates across all the rows. This way it finds the average revenue per order, the smallest order, and the largest order, which it would have not been able to find without aggregating on an order level. You can write this query as follows:
…and the resulting table should look like this:
As you can see, the query is now split into two steps. The first one is your CTE and the second is a <span class = "code">SELECT</span> statement with some aggregating functions used earlier. But this time the temporary table (the CTE) is referenced in the <span class = "code">FROM</span> statement. The CTE is started with a <span class = "code">WITH</span> statement followed by the name given to this temporary table, and an <span class = "code">AS (</span>.
Inside the parenthesis, the <span class = "code">SELECT</span> statement, aggregating functions, and <span class = "code">FROM</span> statement work exactly as you’d expect. The CTE ends with a <span class = "code">)</span>. After the CTE you can also write the <span class = "code">SELECT</span> statement exactly as before, but this time instead of referencing another table in the <span class = "code">FROM</span> statement, you simply reference the CTE. Take a closer look at how CTEs are added to queries with another example:
This example doesn’t make much sense — adding a CTE complicates the query, only to simply <span class = "code">SELECT</span> all the columns in the final <span class = "code">SELECT</span> statement. There’s no point in using CTEs except if you need to perform analysis in several steps. You could get the same results as with the query above by just writing: