Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Summary
15. Summary

Instruction

Good job! Before we move on to the final quiz for this course, let's review what we've learned:

  1. To create a report with history-to-date category revenue values:
    SELECT 
      category_name,
      SUM(amount) AS total_revenue 
    FROM order_items oi
    JOIN products p
      ON oi.product_id = p.product_id
    JOIN categories c
      ON p.category_id = c.category_id
    GROUP BY category_name;
    
  2. To show category revenue values and their relation to the total revenue, use:
    WITH all_categories AS (
      SELECT SUM(amount) AS total_sum
      FROM orders
    )
    SELECT 
      category_name,
      SUM(oi.amount) AS category_revenue,
      SUM(oi.amount) / CAST(ac.total_sum AS DECIMAL(10,2)) AS total_revenue_ratio
    FROM all_categories ac, order_items oi
    JOIN orders o
      ON o.order_id = oi.order_id
    JOIN products p
      ON oi.product_id = p.product_id
    JOIN categories c
      ON p.category_id = c.category_id
    GROUP BY category_name, ac.total_sum;
    
  3. To show the category revenue for selected categories for each time period or category revenue for each category and selected time periods, use SUM(CASE WHEN) along with GROUP BY. For instance:
    SELECT 
      EXTRACT(year FROM order_date) AS revenue_year,
      SUM(oi.amount) AS total_revenue, 
      SUM(CASE WHEN c.category_name='Beverages' THEN oi.amount ELSE 0.0 END) AS beverages_revenue,
      SUM(CASE WHEN c.category_name='Seafood' THEN oi.amount ELSE 0.0 END) AS seafood_revenue 
    FROM order_items oi 
    JOIN orders o
      ON oi.order_id = o.order_id
    JOIN products p
      ON p.product_id = oi.product_id
    JOIN categories c
      ON c.category_id = p.category_id
    GROUP BY EXTRACT(year FROM order_date)
    ORDER BY EXTRACT(year FROM order_date);
    

How about a short quiz to finish?

Exercise

Click Next exercise to continue.