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

Instruction

Good job! There is also an alternative CTE syntax that looks like this:

WITH order_total_prices (order_id, total_price) AS (
  SELECT
    o.order_id, 
    SUM(unit_price * quantity)
  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;

This time, we provided the column names right after the CTE's name, inside a pair of parentheses (WITH order_total_prices (order_id, total_price) AS ...). This way, we didn't have to use the AS keyword to name the columns inside the CTE.

This syntax is completely optional. It's up to you whether you want to use it and it depends on your personal preferences.

Exercise

Try to rewrite the template query so that it uses the syntax presented in the explanation.

Stuck? Here's a hint!

Start with:

WITH products_category_count (category_id, count_products) AS (