E-commerce: Total Orders by Category
MediumPremium
Amazon is a large e-commerce platform where customers can order various items ranging from electronics to clothing.
You're provided with two tables, orders and items, with the following columns:
Write an SQL query to find how many units were ordered in each category in the last 7 days, for each day of the week. Sort alphabetically by item_category.
Desired output example:
This question is part of a 5-part e-commerce question series. The other lessons in the series are linked below:
- E-commerce: Units Ordered Yesterday (1 of 5)
- E-commerce: Units Ordered Last Week (2 of 5)
- E-commerce: Earliest Order by Customer (4 of 5)
- E-commerce: Second Earliest Order (5 of 5)
SELECT
i.item_category,
o.order_date,
SUM(o.order_quantity) AS total_units_ordered
FROM orders o
JOIN items i ON o.item_id = i.item_id
WHERE o.order_date BETWEEN date('now', '-6 days') AND date('now')
GROUP BY i.item_category, o.order_date
ORDER BY i.item_category, o.order_date;
SELECT i.item_category, o.order_date, SUM(o.order_quantity) AS total_units_ordered FROM orders o JOIN items i ON o.item_id = i.item_id WHERE o.order_date >= DATE('now', '-6 days') GROUP BY i.item_category, o.order_date ORDER BY i.item_category ASC, o.order_date ASC;