Total Sales per Product Category
A small e-commerce company wants to understand the performance of its product categories. They have a table of products and another table of sales.
Tables:
products(product_id: integer, category: varchar(50), name: varchar(100))sales(sale_id: integer, product_id: integer, quantity: integer, price_per_unit: numeric(10,2))
Return the category and the total revenue generated by that category. The total revenue for a category is the sum of (quantity * price_per_unit) for all sales of products in that category. If a category has no sales, it should not appear in the result. Order the results by category.
Handle NULLs: Assume category in products and quantity or price_per_unit in sales will not be NULL. Empty results are possible if there are no sales.
Tables
CREATE TABLE products ( product_id integer PRIMARY KEY, category varchar(50) NOT NULL, name varchar(100) NOT NULL ); CREATE TABLE sales ( sale_id integer PRIMARY KEY, product_id integer NOT NULL REFERENCES products(product_id), quantity integer NOT NULL, price_per_unit numeric(10,2) NOT NULL );
Example data
INSERT INTO products (product_id, category, name) VALUES (1, 'Electronics', 'Laptop'), (2, 'Clothing', 'T-Shirt'), (3, 'Electronics', 'Mouse'), (4, 'Home Goods', 'Lamp'), (5, 'Clothing', 'Jeans'); INSERT INTO sales (sale_id, product_id, quantity, price_per_unit) VALUES (101, 1, 2, 1200.00), (102, 2, 5, 25.00), (103, 3, 10, 15.00), (104, 1, 1, 1150.00), (105, 4, 3, 50.00), (106, 2, 2, 30.00);
Your query is also checked on hidden datasets with different data.
Hints
-- Calculate total sales per product category
SELECT category
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.category
ORDER BY p.category;
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.