1. Begin by writing SQL commands to capture usable data.
• Analyze the number of returns by state and describe your findings in your
... [Show More] report.
• Query: SHOW DATABASES;
• Query: USE QuatigrationUpdates;
Query:
SELECT Customers.State AS STATE, COUNT(*) AS RETURN_FREQUENCY
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID GROUP BY STATE
ORDER BY RETURN_FREQUENCY DESC;
Query:
SELECT Customers.State AS STATE, ( COUNT(*) * 100 / (SELECT COUNT(*) FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID)) AS RETURN_PERCENTAGE
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID GROUP BY STATE ORDER BY RETURN_PERCENTAGE DESC;
• Analyze the percentage of returns by product type and describe your findings in your report.
Query:
SELECT SKU AS PRODUCT_SKU, description AS PRODUCT_DESCRIPTION, (COUNT(*) * 100 / (SELECT COUNT(*) FROM Orders INNER JOIN RMA ON
Orders.OrderID = RMA.OrderID)) AS RETURN_PERCENTAGE FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID GROUP BY SKU ORDER BY RETURN_PERCENTAGE DESC;
2. clearly summarize your analysis of the data for stakeholders
• How does the data provide the product manager with usable information?
i. The data provides information per territory to help focus on what products are being sold and returned due to defects by percentage and state. This will allow the product manager to focus on areas that need more attention or changes to products.
• What are the potential flaws in the data that has been presented?
i. Inaccuracy due to human error when data is entered plays a valid role in consistency of information and how true it can be.
ii. System error or data loss due to information not being backed up.
• Are there any limitations on your conclusions, or any other ways of looking at it that you haven’t considered?
i. No.
• Clearly communicate your findings to stakeholders.
i. The data analysis was presented to show the percentage of orders and returns by state as requested. Reviewing the percentages of returned products by state can help focus on the territories that need remediation to reach acceptable levels of returns versus orders. Looking deeper into the problem on the type of defect can help assist in understanding how damages occur with said products per area that may give insight on prevention. [Show Less]