Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Reports with revenue in categories
Revenue in time periods for selected 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 to continue. 