Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
3. Multi-level aggregation – explanation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
Summary

## Instruction

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 (
)
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.

## Exercise

The template code contains the query from the previous exercise. Your task is to modify it to show the average total price after discount. Rename the avg_total_price column to avg_total_discounted_price.

### Stuck? Here's a hint!

Change the inner query. Calculate the total price for an order as:

SUM(unit_price * quantity * (1 - discount))