DEA-C01 Question 123
Single answerYou are working as a data engineer for an e-commerce company. You need to transform raw data stored in an Amazon RDS PostgreSQL database to calculate the total revenue generated by each product category. The relevant tables are: orders (order_id, product_id, quantity, total_price), products (product_id, category_id, product_name), and categories (category_id, category_name). Which SQL query would you use to achieve this?
- A
SELECT c.category_name, SUM(o.total_price) AS total_revenue FROM orders o JOIN products p ON o.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id GROUP BY c.category_name;
- B
SELECT p.product_name, SUM(o.total_price) AS total_revenue FROM orders o JOIN products p ON o.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id GROUP BY p.product_name;
- C
SELECT c.category_name, SUM(o.quantity * o.total_price) AS total_revenue FROM orders o JOIN products p ON o.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id GROUP BY c.category_name;
- D
SELECT c.category_name, SUM(o.total_price) AS total_revenue FROM orders o LEFT JOIN products p ON o.product_id = p.product_id LEFT JOIN categories c ON p.category_id = c.category_id GROUP BY c.category_name;
Show answer and explanation
Correct answer: A
Explanation
To calculate total revenue by product category, you need to join the orders table with the products table (to get product details) and then join with the categories table (to get category details). The grouping should be based on category_name, and the total revenue can be calculated using SUM(total_price). INNER JOIN is used to ensure only rows with matching data in all tables are included.
- A. Correct.
This is the correct query as it calculates the total revenue for each product category by joining the orders, products, and categories tables and grouping by category_name.
- B. Incorrect.
This query incorrectly groups by product_name instead of category_name, which would calculate the total revenue for each product rather than each category.
- C. Incorrect.
This query incorrectly multiplies quantity by total_price, which is unnecessary since total_price already represents the revenue for each order. This would result in incorrect values.
- D. Incorrect.
This query uses LEFT JOINs instead of INNER JOINs, which may include categories or products that have no associated orders, leading to inaccurate results.