About Northwind Traders

Northwind Traders is a fictional company that buys products and resells them to customers.

As an incentive, the company decided to give a bonus to high performing employees. These high performing employees being the ones who are responsible for the five highest order amounts. This analysis will focus on correctly identifying the employees that are eligible for this bonus.

Database Diagram

To get the highest order amounts and the employees who completed them, these following tables will be needed: Employees, Orders, Order_Details and Products. Using SSMS, I have created a diagram showing the tables that will be used for this analysis.

Setting up SQL connection with Rstudio

For this analysis, I used RStudio to connect to my SQLite server. Following R packages were used to create this connection: DBI, odbc, RSQLite. I have downloaded the Northwind Traders database creation sql file from this repository.

con <- odbc::dbConnect(odbc::odbc(), Driver = "SQLite3 ODBC Driver", 
                       Server = "localhost\\SQLITE3", 
                       Database = "Northwind.db", 
                       Trusted_Connection = "True")

Analysis

After examining these tables one by one, the following plan is made. FirstName and LastName columns from the Employees table are needed to have the names of the employees. Then, Employees and Orders tables can be joined using their common column which is EmployeeID. This join is important to be able to connect the EmployeeID to OrderID to figure out which employee is responsible for which order.

SELECT LastName, FirstName, OrderID
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
ORDER BY LastName, FirstName

Using OrderID column Orders and Order_Details tables can be joined, which would help in knowing the quantity of the order.

SELECT LastName, FirstName, Orders.OrderID, Quantity
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
ORDER BY LastName, FirstName

Finally, joining Products table with Order_Details table via ProductID column is necessary to check the price of the product, which will help in finding the highest order amounts.

SELECT LastName, FirstName, Orders.OrderID, Products.ProductID, Quantity, 
Products.UnitPrice
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
ORDER BY LastName, FirstName

To get to the highest sale amount the total cost of each order is needed, which can be easily calculated by first multiplying the Quantity column from the Order_Details table with the UnitPrice column from the Products table and then summing the results per order.

SELECT LastName, FirstName, Orders.OrderID, Products.ProductID, 
sum(Quantity*Products.UnitPrice) AS SalesAmount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
GROUP BY Orders.OrderID

With this last code, the business question is ready to be answered. The only thing left to do is sort this final table’s SalesAmount column in a descending order which can be done by a simple ORDER BY clause. While at it, since only top five highest sales amount is needed, table length can also be limited by 5.

SELECT LastName, FirstName, Orders.OrderID, Products.ProductID, 
sum(Quantity*Products.UnitPrice) AS SalesAmount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
GROUP BY Orders.OrderID
ORDER BY SalesAmount DESC
LIMIT 5

Results are intriguing. Yes, this table shows exactly five of the highest sales amount, however they belong to these four employees only: Awdrew Fuller, Robert King, Nancy Davolio and Steven Buchanan. Since the business question is ambiguous about whether the bonus goes to the five highest sales regardless of the employee count or it goes to the five highest sales by five different employees, it would be ideal to prepare an additional table with the latter scenario in mind.

SELECT LastName, FirstName, Orders.OrderID, Products.ProductID, 
sum(Quantity*Products.UnitPrice) AS SalesAmount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
GROUP BY Orders.OrderID
HAVING Orders.OrderID IN (10865, 11030, 10981, 10372, 10417)
ORDER BY SalesAmount DESC
LIMIT 5

Results

After further simplifying these two final tables, both are ready to be reported to the sales manager.

SELECT LastName, FirstName, sum(Quantity*Products.UnitPrice) AS SalesAmount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
GROUP BY Orders.OrderID
ORDER BY SalesAmount DESC
LIMIT 5

According to the table on top, these four employees are eligible for the bonus:
1. Andrew Fuller
2. Robert King
3. Nancy Davolio
4. Steven Buchanan

According to the table on the bottom, these five employees are eligible for the bonus:
1. Andrew Fuller
2. Robert King
3. Nancy Davolio
4. Steven Buchanan
5. Margaret Peacock

SELECT LastName, FirstName, sum(Quantity*Products.UnitPrice) AS SalesAmount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN Order_Details
ON Orders.OrderID = Order_Details.OrderID
INNER JOIN Products
ON Order_Details.ProductID = Products.ProductID
GROUP BY Orders.OrderID
HAVING Orders.OrderID IN (10865, 11030, 10981, 10372, 10417)
ORDER BY SalesAmount DESC
LIMIT 5