Back-To-School 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 (
  -- 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.

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