Link to Challenge :-
https://docs.google.com/document/d/13VCtoyto9X1PZ74nPI4ZEDdb8hF8LAlcmLH1ZTHxKxE/edit#
Please click on this link to open the Jupyter Notebook in Google Collab and view the solution:
https://colab.research.google.com/drive/1OOOQwi3HO7eoXVFlR0v3tYnZOzVhtRzA?usp=sharing
a) How many orders were shipped by Speedy Express in total?
SELECT COUNT(o.ShipperID) FROM Orders AS o
JOIN Shippers AS s
ON s.ShipperID = o.ShipperID
WHERE ShipperName = 'Speedy Express'
ANSWER: Speedy Express shipped 54 orders in total.
b) What is the last name of the employee with the most orders?
SELECT e.LastName, COUNT(od.ProductID) AS Count
FROM OrderDetails AS od
JOIN Orders AS o
ON o.OrderID = od.OrderID
JOIN Employees AS e
ON e.EmployeeID = o.EmployeeID
GROUP BY e.LastName
ORDER BY Count DESC
LIMIT 1
ANSWER: LastName of the Customer is 'Peacock'.
Total Orders by the Customer: 123
c) What product was ordered the most by customers in Germany?
SELECT ProductName FROM Products WHERE ProductID = (SELECT ProductID FROM (SELECT od.ProductID, COUNT(od.ProductID) AS Count
FROM OrderDetails AS od
JOIN Orders AS o
ON o.OrderID = od.OrderID
JOIN Customers AS c
ON c.CustomerID = o.CustomerID
WHERE c.Country = 'Germany'
GROUP BY od.ProductID
ORDER BY Count DESC
LIMIT 1) as s1)
ANSWER: Most Ordered Product by customers in Germany is 'Gorgonzola Telino'