Northwind — Employee Performance & Sales Analysis

Business Overview

196

Total orders processed

Orders by top employee

Inactive employee found

Co-located supplier pairs

82%

Suppliers in US & Europe

Key Findings

What the data reveals

Q1 — WORKLOAD

Employee Order Processing Volume

Finding

Margaret Peacock processed 40 orders (~20% of total). Anne Dodsworth handled only 6. The top 3 employees processed 102 of 196 total orders.

Insight

Margaret's workload is 6.7× higher than Anne's, suggesting uneven task assignment rather than normal performance differences.

Recommendation

Confirm whether low-volume employees are new hires or part-time. Introduce rotation policies and mentorship pairing with high performers.

Q2 — DATA QUALITY

Inactive Employee Detection

Finding

Adam West (EmployeeID 10) has zero recorded orders. His notes read "an old chum" and his age is significantly higher than the rest of the team.

Insight

Adam likely holds a non-operational or retired role. His inclusion distorts team performance metrics and KPIs.

Recommendation

Confirm status with HR. If inactive, archive his record. If advisory, document the role clearly to prevent future misclassification.

Q3 — RETENTION

Customer Relationship Breadth

Finding

Margaret served 31 unique customers (~42% of 74 total) with 9 repeat orders. Anne served 6 customers with zero repeat orders.

Insight

Higher order volume correlates with stronger customer retention — likely driven by experience, trust, and customer communication skills.

Recommendation

Use top performers as internal benchmarks. Introduce shadowing and mentoring programs. Formalise high-performer behaviours into training guidelines.

Q4 — RISK

Supplier Geographic Concentration

Finding

17 supplier pairs are co-located. USA accounts for 6 pairs (35.3%), Europe for 8 pairs (47.1%). Asia and Australia combined represent only 2 pairs.

Insight

Over 82% of co-located supplier relationships sit in the US and Europe, creating elevated exposure to trade disruptions and regional instability.

Recommendation

Pursue geographic diversification. Target 20–25% of supplier relationships from Asia-Pacific within 18 months. Identify backup suppliers for critical categories.

Q5 — OPERATIONS

Operational Order Monitoring

Finding

196 orders across 7 months (July 1996 – Feb 1997). Ernst Handel recorded the highest customer order count at 9. Nearly half of customers placed multiple orders.

Insight

Confirms patterns of customer loyalty and over-dependency on a small group of high-performing employees for order volume.

Recommendation

Integrate into routine monthly reporting for Sales and Customer Success teams to track retention, flag declining accounts, and support workload balancing.

SQL Queries

The code behind the analysis

q1_employee_orders_count.sql Order volume per employee

▶ expand

-- Which employees have processed orders, and how many?
SELECT
  Employees.EmployeeID,
  CONCAT(Employees.FirstName, ' ', Employees.LastName) AS 'Employee Name',
  COUNT(Orders.OrderID) AS 'Number of Orders'
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName
ORDER BY COUNT(Orders.OrderID) DESC;

q2_inactive_employees.sql Employees with no recorded orders

▶ expand

-- Are there employees who have never processed an order?
SELECT
  Employees.EmployeeID,
  CONCAT(Employees.FirstName, ' ', Employees.LastName) AS 'Employee Name'
FROM Employees
LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.OrderID IS NULL
ORDER BY Employees.EmployeeID;

q3_employee_unique_customers.sql Unique customers served per employee

▶ expand

-- Which employee served the most unique customers?
SELECT
  Employees.EmployeeID,
  CONCAT(Employees.FirstName, ' ', Employees.LastName) AS 'Employee Name',
  COUNT(DISTINCT Customers.CustomerID) AS 'Unique Customers'
FROM ((Orders
  INNER JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID)
  INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID)
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName
ORDER BY COUNT(DISTINCT Customers.CustomerID) DESC;

q4_suppliers_pair_by_country.sql Co-located supplier pairs by country

▶ expand

-- Which pairs of suppliers are located in the same country?
SELECT
  A.SupplierName AS 'Supplier 1',
  B.SupplierName AS 'Supplier 2',
  A.Country
FROM Suppliers A, Suppliers B
WHERE A.Country = B.Country
AND A.SupplierID < B.SupplierID
ORDER BY A.Country;

q5_order_employee_customer_details.sql Full order view with employee and customer details

▶ expand

-- Show all orders with employee name, customer name, and order date.
SELECT
  Customers.CustomerName AS 'Customer Name',
  CONCAT(Employees.FirstName, ' ', Employees.LastName) AS 'Employee Name',
  Orders.OrderID,
  Orders.OrderDate
FROM ((Orders
  INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID)
  INNER JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Customers.CustomerName ASC;

Data Quality Notes

Known issues & handling

Text fields contain trailing spaces (e.g. "Germany "). Queries intentionally account for this whitespace in filters.
Certain ID fields are stored as text and may require CAST(... AS UNSIGNED) for correct numeric sorting.
EmployeeID 10 (Adam West) has no order records. His inclusion in unfiltered queries distorts team-wide performance metrics.

Conclusion

Three key themes

Workload Imbalance

A small group of employees processes the majority of orders. Redistribution and mentorship are needed to reduce burnout risk and unlock underutilised capacity.

Performance & Retention Link

Higher order volume strongly correlates with broader customer coverage and repeat orders. Top performer behaviours should be formalised into training and onboarding.

Supply Chain Concentration Risk

Over 82% of co-located supplier pairs sit in the US and Europe. Geographic diversification toward Asia-Pacific is critical for long-term supply chain resilience.