The code in the previous exercise used an SQL concept known as a CTE, or a common table expression. Think of it as a temporary set of rows that you define and use later in the same query. CTEs are similar to subqueries.

The most basic syntax of any common table expression looks like this:

WITH some_name AS (
  -- your CTE
)
SELECT
  ... 
FROM some_name

You need to give your CTE a name (we used some_name in the example) and define it within a pair of parentheses. Then, once you close the bracket, you can select columns from the CTE as if it were a table. We will refer to the CTE as the "inner query" and the part after it as the "outer query." Note that you need to define your CTE first – i.e., before the outer query's SELECT.

Back to our example:

WITH OrderTotalPrices AS (
  SELECT
    O.OrderID, 
    SUM(UnitPrice * Quantity) AS TotalPrice
  FROM Orders O
  JOIN OrderItems OI
    ON O.OrderID = OI.OrderID
  GROUP BY O.OrderID
)

SELECT
  AVG(TotalPrice) AS AvgTotalPrice
FROM OrderTotalPrices;

Here, the CTE is called OrderTotalPrices and allows us to access two columns: OrderID and TotalPrice. In the outer query, we aggregate the TotalPrice column using AVG(TotalPrice). As a result, we'll get a single number in the report.