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 parentheses, 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 order_total_prices AS (
  SELECT
    o.order_id, 
    SUM(unit_price * quantity) AS total_price
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  GROUP BY o.order_id
)

SELECT
  AVG(total_price) AS avg_total_price
FROM order_total_prices;

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