SQL Analysis of Product Returns by State and Type

DAD 220 Professor Wilson Melissa Galvan 22 nd October 2023 1. Begin by writing SQL commands to capture usable data (which you’ve preloaded into Codio) for your analysis. I have been using commands: chmod +x change_perm.sh press enter ./change_perm.sh press enter Mysql press enter Show databases;

Use QuantigrationUpdates; press enter Select count(*) from Collaborators; press enter Select count(*) from Orders; press enter Select count(*) from RMA; press enter These are the commands I used to begin my SQL. Then I used count(*) functions to help ensure that data was loaded correctly and organized. 1. Specifically, the product manager wants you to analyze the following: o Analyze the number of returns by state and describe your findings in your report.

Commands: SELECT Collaborators.State AS State, Count(*) AS Return_Number FROM ‘Orders’ INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Collaborators ON Collaborators.CollaboratorID = Orders. CollaboratorID GROUP BY State ORDER BY Return_Number DESC LIMIT 15; Commands: SELECT Collaborators.State AS State, Count(*) AS Return_Number FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Collaborators ON Collaborators.CollaboratorID = Orders. CollaboratorID

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

GROUP BY State ORDER BY Return_Number ASC LIMIT 15; Findings for both: In the results of the analysis of returns by state, you come across intriguing patterns found within the data. By organizing them by states with the highest return count. We have found Massachusetts -972, Arkansas - 844, Oregon, West Virginia, and Alabama are the highest return states. This makes it seem as if there are specific factors causing a high return status. The lowest return states are as follows South Carolina, New Jersey, Colorado, Georgia, and Nebraska. The lowest return of all is South Carolina. This could be due to multiple reasons. It is a top priority to dig deeper into any underlying reasons. o Analyze the percentage of returns by product type and describe your findings in your report. o

Commands: SELECT SKU AS PRODUCT_SKU, Description AS Product_Description, COUNT(*) AS Total, (COUNT(*)/(SELECT COUNT(*) FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID) * 100) AS Percentages_of_Returns FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID WHERE UPPER(Status) = '‘COMPLETE’' GROUP BY Product_SKU ORDER BY Percentages_of_Returns DESC LIMIT 10; 2. In your report, clearly summarize your analysis of the data for stakeholders . Include screenshots of the results of each query. When summarizing results, you may want to consider the following questions: The In order to investigate why a particular item is returned the most frequently or why a particular state has so many returns, this data can help identify the region that experiences the greatest number of returns. One of the reports' potential weaknesses is that it might contain inaccurate information if the date is distorted or entered wrongly. In order to narrow down the reasons why the products were being returned, some other perspectives that might be taken into account include possibly looking into what the cause for the item return was.4. Based on my research and the data acquired, I discovered that Massachusetts appears to have a high rate of orders that are returned. I advise more investigation to learn why this is the case.

o How does the data provide the product manager with usable information? o What are the potential flaws in the data that has been presented? o Are there any limitations on your conclusions, or any other ways of looking at it that you haven’t considered? Clearly communicate your findings to stakeholders.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

dad 220 7-1 done project

Related Documents