DEA-C01 Question 332
Single answerYou are working as a data engineer for a company that uses Amazon Redshift as their data warehouse. You are tasked with writing a SQL query to generate a report that includes the total sales amount and the number of orders for each customer. The data is stored in two tables: customers and orders. The customers table has columns customer_id and customer_name, and the orders table has columns order_id, customer_id, and order_amount. Which SQL query will correctly produce the desired report?
- A
SELECT c.customer_name, SUM(o.order_amount) AS total_sales, COUNT(o.order_id) AS order_count FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name;
- B
SELECT c.customer_name, SUM(o.order_amount) AS total_sales, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
- C
SELECT c.customer_name, SUM(o.order_amount) AS total_sales, COUNT(o.order_amount) AS order_count FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;
- D
SELECT customer_name, order_amount, COUNT(order_id) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
Show answer and explanation
Correct answer: A
Explanation
To generate the desired report, we need to join the customers and orders tables using an INNER JOIN, calculate the total sales using SUM, count the orders using COUNT, and group the results by customer_name. Option 1 does all of these correctly, whereas the other options introduce errors or use incorrect logic.
- A. Correct.
Correct. This query uses an INNER JOIN to combine the
customersandorderstables, aggregates theorder_amountto calculate total sales for each customer, counts the number of orders, and groups the results bycustomer_name. - B. Incorrect.
Incorrect. This query uses a LEFT JOIN, which is unnecessary since the task requires only those customers who have placed orders. A LEFT JOIN may include customers with no orders, leading to incorrect results.
- C. Incorrect.
Incorrect. The query incorrectly groups by
customer_idinstead ofcustomer_name, which would lead to an error or incorrect grouping in the result. - D. Incorrect.
Incorrect. This query does not use any aggregation function on
order_amount(e.g., SUM) and does not group the results, which would lead to an invalid SQL query.