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:
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;