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?