Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Reports with revenue in categories


Well done! It's time for an additional exercise.


Create a report of the category revenue for orders shipped to Germany. Show three columns: category_name, category_revenue, and total_revenue_ratio. The last column should show the ratio of category revenue to total revenue generated by orders shipped to Germany.

Stuck? Here's a hint!

Create a temporary table using:

WITH all_categories AS (
  SELECT SUM(amount) AS total_sum
  FROM orders
  WHERE ship_country = 'Germany'

Calculate the last column as:

SUM(oi.amount) / CAST(ac.total_sum AS DECIMAL(10,2)) AS total_revenue_ratio