7-1 Project Two: Analyzing Databases PDF

Preview

Summary

Analyze the results of queries constructed to address data requirements...

Description

Katrina Kritsings 10/17/2021 1) Analyze the frequency of returns by state, and describe your findings in your report. To find out how many returns were made in each state, I entered the command; 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;

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;

This gave me the results that Massachusetts has the highest return frequency at 972. The second highest was Arkansas at 844, and the third was Oregon with 840. 2) Analyze the percentage of returns by product type and describe your findings in your report. SELECT sku AS PRODUCT_SKU, description AS PRODUCT_DESCRIPTION, COUNT(*) AS RETURN_FREQUENCY FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID GROUP BY PRODUCT_SKU ORDER BY RETURN_FREQUENCY DESC\G

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 PRODUCT_SKU ORDER BY RETURN_PERCENTAGE DESC\G

What I found was that the product called Basic Switch 10/100/1000 BaseT 48 port had the most return frequency with the return amount at 8282. 3) a) How does the data provide the product manager with usable information? This data helps represent the area where most of the returns happen so that further investigation can be made to understand why this particular place has the most returns. This data also helps explain the item that has the most returns and what can be done to fix the issue.

b) What are the potential flaws in the data that has been presented? The data could be entered incorrectly which would represent false information when gathering data reports.

c) Are there any limitations on your conclusions, or any angles you haven’t considered? I had limitation on my product data, I could further investigate what the issue was for the returns and what item had the most return in each state which could represent different conclusions. 4) Clearly communicate your findings to stakeholders. From the data that was gathered, there seems to be a high number of returns in the state of Massachusetts, further investigation could be made to learn what is causing the high number so that the issue can be solved. So far the product with the highest

return rate happens to be product Basic Switch 10/100/1000 BaseT 48 port, this information can be useful in seeing if there is a correlation with this product and the number of returns that were made in the state of Massachusetts. This could help break down the cause of why the items are being returned and what could be changed to help fix this error....