Data Science Intern Challenge for Shopify

Question One

a. Based on the initial AOV provided in the assignment, it was clear that there must be outliers of some kind skewing the data. With some data exploration and cleaning, it was easy to identify the outliers and remove them from the data in order to calculate an AOV that more accurately reflects sales in the shoe stores. This can be seen in question_one.ipynb.

b. The metric that I would report for this dataset really depends on the question that needs to be answered or the problem that needs to be solved. Are we looking to evaluate the performance of different stores? Find a pattern in AOV as it relates to item prices? Determine whether customers who spend more in one transaction or customers who make multiple smaller purchases actually spend more in total on average? Without knowing what the goal is, it would be pointless to pick a metric at random.

c. After cleaning the data, the AOV as originally requested turns out to be $302.58.

Question Two

a. After a quick select * query on the Shippers table to check the ShipperID for Speedy Express, the query "SELECT count(OrderID) FROM orders where ShipperID = 1;" tells us that Speedy Express shipped a total of 54 packages.

b. A more complex query including a join was needed for part b. The following query reveals that Callahan had the most orders, coming in at 216: "SELECT Orders.EmployeeID, Employees.LastName, sum(Orders.EmployeeID) as Total FROM Orders LEFT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID GROUP BY Orders.EmployeeID ORDER BY Total DESC;".

c. This required the most complex of the three SQL queries, utilizing multple joins: "SELECT OrderDetails.OrderID, OrderDetails.ProductID, SUM(OrderDetails.Quantity) AS TotalUnitsOrdered, Products.ProductName, Orders.OrderID, Orders.CustomerID, Customers.Country FROM OrderDetails LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID LEFT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.Country = "Germany" GROUP BY Products.ProductName ORDER BY TotalUnitsOrdered DESC". This shows us that customers located in Germany ordered more Boston Crab Meat than any other product, purchasing a total of 160 units of it.