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

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Revenue Trend Analysis in SQL

Introduction
Reports with revenue in categories
Summary

Instruction

Nice! We can also use additional filters when we calculate the total revenue in each category. For instance, if we wanted to show the total revenue in each category for orders placed in 2016, we could use the following query:

SELECT 
  category_name,
  SUM(oi.amount) AS total_revenue 
FROM 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
WHERE order_date >= '2016-01-01'
  AND order_date < '2017-01-01'
GROUP BY category_name;

Note that to filter the order_date column, we joined the order_items and orders tables.

Exercise

Generate a history-to-date category revenue report based on orders shipped to the United States. The names of the columns should be category_name and total_revenue.

Stuck? Here's a hint!

Use the ship_country column. It should have the value 'USA'.