Very well done! Now, our previous report showed the categories as columns and the time periods as rows. We can switch this, showing the time periods as columns and the categories as rows. The report will look like this:
Take a look at the query:
SUM(oi.amount) AS overall_category_revenue,
SUM(CASE WHEN order_date >= '2016-01-01' AND order_date < '2017-01-01' THEN oi.amount ELSE 0.0 END) AS revenue_2016,
SUM(CASE WHEN order_date >= '2017-01-01' AND order_date < '2018-01-01' THEN oi.amount ELSE 0.0 END) AS revenue_2017,
SUM(CASE WHEN order_date >= '2018-01-01' AND order_date < '2019-01-01' THEN oi.amount ELSE 0.0 END) AS revenue_2018
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
This time we don't group by time periods. Instead, we group by product categories. On the other hand, we have to define separate columns, using
SUM(CASE WHEN...) for each period we want to compare.
This report format is useful when we have many categories to compare but few time periods.