Perfect! Next, we'd like to compare revenue values in selected categories year-to-year, quarter-to-quarter, etc. This way, we can analyze whether the category revenue increases or decreases over time.
The report should look like this:
We could use the following query:
EXTRACT(year FROM order_date) AS revenue_year,
SUM(oi.amount) AS total_revenue,
WHEN c.category_name = 'Beverages'
END) AS beverages_revenue,
WHEN c.category_name = 'Seafood'
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);
As you can see, the second column contains the total revenue, which is a simple
SUM() of all the values in the
amount column. However, we also added two columns which use the
SUM(CASE WHEN...) construction. These will select only the
amount values for specific categories.
This type of report is typically used when there are few categories to compare over many reporting periods.