shopify-challenge-data-science

Data science shopify Challenge

Question 1

1. Possibilities could be that the average was calculated wrongly, Simple mistakes can happen. Maybe we can split the average value weekly and see if the values are still odd.

2. Well, I ran a few queries as you can see here and I noticed that a SINGLE user_id, ordered 17 seperated orders, of 2000 items, having a total of 704000$ every order. This obviously will be accounted for. So a solution would be to exclude any orders from this user_id. And recalculate the AOV.

3. The calculation excluding this user_id goes to: 754.09$, as seen here

Question 2

1. SELECT count(*) FROM Orders WHERE ShipperID =
   (SELECT ShipperID FROM Shippers WHERE ShipperName IS 'Speedy Express');
   -- Which yields 54 --

2. SELECT LastName
   FROM Employees WHERE EmployeeID =
   (SELECT count() FROM Orders GROUP BY CustomerID ORDER BY count() DESC LIMIT 1);
   -- Which yields theh answer: West --

3. SELECT sum(Quantity) AS TotalOrdered, ProductName
   FROM OrderDetails
   INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
   INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
   INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
   WHERE Customers.Country = 'Germany'
   GROUP BY OrderDetails.ProductID
   ORDER BY sum(OrderDetails.Quantity) DESC
   LIMIT 1;
   -- Which yields the answer: Boston Crab Meat