Analyzing Northwind Sales with MySQL

“Northwind Traders — Where Commerce Meets SQL”

👋Hello Data Champs! 😎

Thank you so much for stopping by. I’m super excited to share my very first SQL practice project with you, and I hope you find it super insightful.

“What’s the big deal about data? 🤔”

The truth is, data has become one of the most valuable assets in today’s world, driving decisions in business, technology, and even our daily lives. But data on its own is just numbers and text, the real power comes when we can analyze it and uncover meaningful insights. That’s exactly what I set out to do in this project using SQL.

📂Dataset and its Source

For this project, I worked with the Northwind Traders dataset, which I downloaded from Maven Analytics. The Northwind database is a classic sample dataset originally created by Microsoft.

According to Maven, the dataset contains:

“Sales & order data for Northwind Traders, a fictitious gourmet food supplier, including information on customers, products, orders, shippers, employees, order details and product categories.”

The dataset was downloaded as a CSV file and uploaded into MySQL for analysis. You can access the dataset here: Northwind Dataset Download.

Entity Relationship Diagram

Before jumping into the analysis, it helps to first understand how the dataset is organized. The Entity Relationship (ER) diagram provides a visual representation of the tables in the Northwind database and how they are connected through relationships (primary, foreign, and composite keys).

Here’s the ER diagram for the Northwind dataset I used:

Entity Relationship Diagram

⚙️Data Preparation

After downloading the dataset, I had a total of 7 CSV files, each representing the different tables in the Northwind database. These files contain information about the customers, orders, products, employees, product categories, shippers, and order details. I uploaded these files into MySQL Workbench to create my working database. The steps taken during the data preparation process includes:

1. Checking the Data Types:

Before running any queries, one of the first steps is to ensure that the imported data is stored in the appropriate data type. This helps maintain data accuracy and prevents errors during analysis.

a. Orders Table:

The orders table in the Northwind database had three columns that were not assigned the appropriate data types. These are the orderDate, shippedDate, and requiredDate columns. They were initially stored as text, but since they represent dates, I reformatted them to the appropriate date type to support time-based analysis.

The syntax for reformatting the orderDate column is:

/* Convert orderDate from text format (DD/MM/YYYY) into proper DATE format.*/
UPDATE orders
SET orderDate = DATE_FORMAT(STR_TO_DATE(TRIM(orderDate), '%d/%m/%Y'), 
                            '%Y-%m-%d')
WHERE orderDate IS NOT NULL AND TRIM(orderDate) <> '';

/* Modify the column to DATE type with a NULL constraint */
ALTER TABLE orders 
MODIFY orderDate DATE NULL;

The syntax for reformatting the requiredDate column is:

/* Convert requiredDate from text format (DD/MM/YYYY) into proper DATE format.*/
UPDATE orders
SET requiredDate = DATE_FORMAT(STR_TO_DATE(TRIM(requiredDate), '%d/%m/%Y'), 
                               '%Y-%m-%d')
WHERE requiredDate IS NOT NULL AND TRIM(requiredDate) <> '';

/* Modify the column to DATE type with a NULL constraint */
ALTER TABLE orders 
MODIFY requiredDate DATE NULL;

The syntax for reformatting the shippedDate column is:

/* Convert shippedDate from text format (DD/MM/YYYY) into proper DATE format */
UPDATE orders
SET shippedDate = DATE_FORMAT(STR_TO_DATE(TRIM(shippedDate), '%d/%m/%Y'), 
                              '%Y-%m-%d')
WHERE shippedDate IS NOT NULL AND TRIM(shippedDate) <> '';

/* Handle empty string values by replacing them with NULL */
UPDATE orders
SET shippedDate = NULL
WHERE TRIM(shippedDate) = '';

/* Modify shippedDate column to DATE type allowing NULL values */
ALTER TABLE orders 
MODIFY shippedDate DATE NULL;

b. Customers Table:

The customerIDcolumn in this table was reformatted to a fixed length (CHAR (5)) because it contains the initials of the customer names. It was also set as the primary key to ensure uniqueness and integrity.

The syntax for reformatting the customerIDcolumn is:

/*This query updates the customerID column to a fixed length of 5 characters 
(since it stores customer initials) and sets it as the primary key.*/
ALTER TABLE customers
MODIFY customerID CHAR(5) NOT NULL,
ADD PRIMARY KEY (customerID);

2. Checking for NULLs and Blank Spaces:

This is an important step in the data preparation stage. The presence of NULL values and blank spaces could be as a result of human errors, incomplete data entry, and system default during data importation. If not properly handled, they can lead to inaccurate results, or inconsistencies in analysis. In the Northwind database, the employeestable had a blank space under the reportsTocolumn.

a. Employees Table:

Under this table, the reportsTocolumn had a blank space in the second row. To tackle this issue, we need to replace the blank space with a NULL. The syntax for this is:

/*Replace blank space with a NULL value.*/
UPDATE employees
SET reportsTo = NULL
WHERE TRIM(reportsTo) = '';

3. Assigning Primary and Foreign Keys for Data Integrity:

This stage is a crucial part of data preparation, especially when working with datasets imported into Relational Database Management Systems (RDBMS) such as MySQL.

One of the key steps in ensuring data integrity is the assignment of primary keys and foreign keys. Primary keys uniquely identify each record within a table, preventing duplication and enabling efficient retrieval of information. Foreign keys, on the other hand, establish relationships between tables, ensuring that records are logically connected and consistent across the database.

The syntax for assigning primary and foreign keys in a table is:

/* Add a primary key constraint on the productID column in the products 
   table.*/
ALTER TABLE products
ADD PRIMARY KEY (productID);

/* Add a foreign key constraint on the categoryID column in the products table, 
   linking it to the categoryID column in the categories table */
ALTER TABLE products
ADD CONSTRAINT fk_category
FOREIGN KEY (categoryID) REFERENCES categories(categoryID);

This approach can similarly be extended to other tables within the database, such as linking the orders table to the customers table or establishing hierarchical relationships in the employees table through the reportsTo column. Applying these constraints consistently ensures referential integrity and a well-structured relational schema.

4. Checking for Duplicates:

During the data preparation stage, it is also common practice to check for duplicate records, as they can distort analysis results and lead to misleading insights. However, after reviewing the dataset, no duplicate rows were found across the tables. This ensured that the data was unique and consistent without requiring further deduplication steps.

In conclusion, the data preparation phase enabled the identification and resolution of issues like NULL values, blank spaces, and missing constraints. This process ensured the dataset was well-structured, consistent, and dependable for further analysis. With this solid groundwork, we are ready to move on to the next step: querying the database to uncover valuable insights.

Querying the Database for Insights

Having completed the heavy lifting of data preparation, the next step is to query the database for meaningful insights. In this phase, I take on the role of a data analyst for the Northwind trading company. Management has handed me the company’s historical transaction data with the expectation of uncovering patterns, trends, and relationships that can guide strategic decision-making.

The analysis is structured around business-driven questions spanning customer behavior, product performance, shipping efficiency, employee productivity, and sales trends. Each question is addressed with SQL queries, leading to insights that support management’s decisions.

1. Customer Insights & Sales Performance:

Business Question: Management is interested in identifying the top 5 customers globally who generate the highest revenue, along with their respective countries and cities.

SQL Query:

WITH customer_revenue AS (
   SELECT
      cus.customerID,
      cus.companyName,
      cus.country,
      cus.city,
      ROUND(SUM(ordet.unitPrice * ordet.quantity * (1 - ordet.Discount)), 2) 
     AS total_revenue
   FROM
      customers AS cus
   LEFT JOIN 
      orders AS ord
    ON 
      cus.customerID = ord.customerID
   LEFT JOIN 
      order_details AS ordet
    ON 
      ord.orderID = ordet.orderID
   GROUP BY
       1,2,3,4
)

SELECT
 customerID,
 companyName,
 country,
 city,
 total_revenue
FROM
 customer_revenue
ORDER BY
 total_revenue DESC
LIMIT 5;

Result & Insight:

This result shows the top 5 customers globally ranked by total revenue. It highlights which companies contribute most to sales performance and shows their locations (country and city), enabling management to see where key revenue drivers are concentrated.

Business Question: Concerns have been raised about customer retention. There is a need to identify customers who have placed only a few orders or have not made purchases within the 180 days leading up to May 6, 2015, as these customers may be at risk of churn.

Get Favour Isaac’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SQL Query:

WITH customer_churn AS (
   SELECT
      cus.customerID,
      cus.companyName,
      COUNT(ord.orderID) AS order_count,
      MAX(ord.orderDate) AS last_order_date,
   CASE 
       WHEN MAX(ord.orderDate) IS NULL
        OR MAX(ord.orderDate) < DATE('2015-05-06') - INTERVAL 180 DAY
        OR COUNT(ord.orderID) <= 1
       THEN 'At Risk of Churn'
        ELSE 'Active'
      END AS churn_status
   FROM
      customers AS cus
   LEFT JOIN
      orders AS ord
   ON cus.customerID = ord.customerID
   GROUP BY
      1, 2
   ORDER BY
      last_order_date ASC
)

SELECT 
 *
FROM
 customer_churn
WHERE
 churn_status = 'At Risk of Churn';

Result & Insight:

This result shows a list of customers identified as being at risk of churn based on low order frequency or inactivity within the last 180 days before May 6, 2015.

2. Product Performance & Category Insights:

Business Question: The product team needs to know which categories contribute most to overall revenue and profitability.

SQL Query:

SELECT 
  cat.categoryID,
  cat.categoryName,
  ROUND(SUM(ord.unitPrice * ord.quantity), 2) AS total_revenue
FROM
  products AS pro
LEFT JOIN
  order_details AS ord
ON
  pro.productID = ord.productID
LEFT JOIN
  categories AS cat
ON
  pro.categoryID = cat.categoryID
GROUP BY
  1,2
ORDER BY
  3 DESC;

Result & Insight:

This result shows the total revenue contributed by each of the eight product categories. Beverages (categoryID = 1) stands out as the leading contributor to overall revenue.

Business Question: Inventory optimization is critical. Management wants to identify products with consistently low sales volumes, defined as those with fewer than 100 total units sold, that may be considered for discontinuation.

SQL Query:

WITH prod_stats AS (    
  SELECT 
      p.productID,
      p.productName,
      SUM(od.quantity) AS total_units_sold,
      ROUND(SUM(od.quantity * od.unitPrice), 2) AS total_revenue,
    
      CASE 
          WHEN SUM(od.quantity) < 100 THEN 'Low Sales'
          WHEN SUM(od.quantity) BETWEEN 100 AND 500 
        THEN 'Medium Sales'
          ELSE 'High Sales'
      END AS sales_category,
    
    CASE 
        WHEN SUM(od.quantity) < 100 
      THEN 'Consider for Discontinuation'
        ELSE 'Keep Product'
    END AS product_status

  FROM 
   products AS p
  JOIN 
   order_details AS od 
  ON 
   p.productID = od.productID
  GROUP BY 
   p.productID, p.productName
  ORDER BY 
   total_units_sold ASC
)
    
SELECT
     *
FROM
     prod_stats
WHERE 
     sales_category = 'Low Sales'
       AND product_status = 'Consider for Discontinuation';

Result & Insight:

The result highlights Mishi Kobe Niku (ProductID: 9) as the product with the lowest total units sold, indicating that it should be considered for discontinuation.

3. Shipping & Delivery Performance:

Business Question: Operations are evaluating shipper performance. Which shipping companies handle the highest number of orders, and what is their average delivery time?

SQL Query:

WITH shipper_performance AS (
    SELECT
       shipperID,
       COUNT(orderID) AS number_of_orders,
       AVG(DATEDIFF(shippedDate, orderDate)) AS avg_delivery_time
    FROM
       orders
    GROUP BY
        shipperID
)
    
SELECT
    shipper_performance.shipperID,
    companyName,
    number_of_orders,
    avg_delivery_time
FROM
 shippers AS ship
LEFT JOIN 
 shipper_performance
ON
 ship.shipperID = shipper_performance.shipperID
ORDER BY 
 number_of_orders DESC;

Result & Insight:

The result shows the order distribution and average delivery time by shipper. Federal Shipping delivers the fastest on average, while United Package handles the highest number of orders but with the slowest delivery times.

Business Question: Management also wants to assess shipping delays. Which shippers handle the most orders, and how do they compare in terms of average delivery times and the share of orders exceeding the 7-day delivery benchmark?

SQL Query:

SELECT
    ship.shipperID,
    ship.companyName,
    COUNT(orderID) AS total_orders,
    AVG(DATEDIFF(shippedDate, orderDate)) AS avg_delivery_time,
    SUM(CASE 
          WHEN DATEDIFF(shippedDate, orderDate) > 7 
         THEN 1 ELSE 0 END) 
       AS delayed_orders,
    ROUND(100.0 * SUM(CASE 
                         WHEN DATEDIFF(shippedDate, orderDate) > 7 
                       THEN 1 ELSE 0 END) / COUNT(orderID), 2) 
       AS delay_rate
FROM
    orders AS ord
LEFT JOIN
 shippers AS ship
ON
 ord.shipperID = ship.shipperID
GROUP BY
    shipperID, companyName
ORDER BY
    delay_rate DESC;

Result & Insight:

This result highlights clear differences in shipper performance. Federal Shipping demonstrates the quickest turnaround times, delivering orders faster than its competitors. In contrast, United Package handles the largest share of orders but also records the highest delay rate, suggesting a trade-off between volume and reliability.

4. Employee Productivity & Operations Efficiency:

Business Question: HR and operations want to identify employees who process the highest number of orders, along with their average order value.

SQL Query:

WITH ord_val AS (
 SELECT
        ord.employeeID,
        COUNT(DISTINCT ords.orderID) AS no_of_orders,
        SUM(ords.unitPrice * ords.quantity) AS total_sales
 FROM 
  orders AS ord
 LEFT JOIN
  order_details AS ords
 ON
  ord.orderID = ords.orderID
 GROUP BY
  1
)    
SELECT
   emp.employeeID,
    employeeName,
    no_of_orders,
    ROUND(ov.total_sales / ov.no_of_orders, 2) AS avg_ord_value
FROM
    ord_val AS ov
LEFT JOIN
 employees AS emp
ON
 ov.employeeID = emp.employeeID
ORDER BY
 3 DESC;

Result & Insight:

This result highlights employee order performance, showing Margaret Peacock processed the highest number of orders (156) with a strong average value, while Robert King, despite handling fewer orders, achieved the highest average order value at 1,962.44. The table illustrates how both order volume and order value vary among employees.

Business Question: There is interest in measuring employee efficiency. How long does it typically take employees to process orders from placement to shipment?

SQL Query:

SELECT
    emp.employeeID,
    emp.employeeName,
    AVG(DATEDIFF(ord.shippedDate, ord.orderDate)) AS avg_processing_days,
    MIN(DATEDIFF(ord.shippedDate, ord.orderDate)) AS fastest_days,
    MAX(DATEDIFF(ord.shippedDate, ord.orderDate)) AS slowest_days,
    COUNT(ord.orderID) AS total_orders
FROM
    employees AS emp
INNER JOIN
    orders AS ord
ON
    emp.employeeID = ord.employeeID
GROUP BY
    emp.employeeID, emp.employeeName
ORDER BY
    avg_processing_days ASC;

Result & Insights:

This result shows the overall efficiency of employees in processing orders, with average turnaround times generally ranging between 7 and 11 days. Most employees fall within the 7–9 day range, indicating consistent performance across the team. Steven Buchanan achieved the fastest average turnaround of 7.02 days, while Anne Dodsworth recorded the slowest at 10.86 days. Janet Leverling handled the highest workload with 127 orders, showing strong capacity alongside steady efficiency.

5. Sales Trend & Strategic Insights:

Business Question: The strategy team is examining seasonality. Are there particular months or periods with peak sales activity?

SQL Query:

SELECT
 Month,
    Year,
    ROUND(SUM(Sales), 2) AS Total_Sales
FROM
     (SELECT 
        MONTHNAME(orderDate) AS Month,
        YEAR(orderDate) AS Year,
        ROUND((unitPrice * quantity), 2) AS sales
     FROM
        orders
     LEFT JOIN
        order_details
     ON 
        orders.orderID = order_details.orderID
     ORDER BY 
        2 ASC
              ) AS sub_sales
GROUP BY
 1,2
ORDER BY
 Total_sales DESC
LIMIT
 5;

Result & Insights:

This result shows the monthly sales trend, with steady growth from December 2014 to April 2015, where sales peaked at 134,630.56 in April 2015.

Business Question: Management wants to evaluate year-over-year growth. How have yearly sales changed over time in regions such as the USA, UK, and Belgium, and what is the year-over-year growth compared to the previous year?

SQL Query:

WITH sales_cte AS (
   SELECT
      country,
      year,
      sales,
     LAG(sales,1) OVER (
                       PARTITION BY country
                        ORDER BY year) AS Previous_sales
   FROM   
        (SELECT
             country,
             YEAR(orderDate) AS year,
             ROUND(SUM(ROUND((unitprice * quantity),2)),2) AS sales
        FROM
             orders AS ord
        LEFT JOIN
             customers AS cus
        ON
             ord.customerID = cus.customerID
        LEFT JOIN
             order_details AS ordet
        ON
             ord.orderID = ordet.orderID
        GROUP BY
              1, 2
        ORDER BY
               3 DESC
                       ) AS sub_salestrend
)
SELECT
    country,
    year,
    sales,
    IFNULL(Previous_sales, 0) AS previous_sales,
    CASE
          WHEN previous_sales IS NULL THEN 0 
       ELSE ROUND((sales - Previous_sales),2) 
   END AS sales_diff_over_year
FROM
 sales_cte
WHERE country IN 
 ('Belgium', 'USA', 'UK')
ORDER BY
 country, year;

Result & Insights:

This result shows the year-over-year sales trend for Belgium, the UK, and the USA between 2013 and 2015, highlighting strong growth in Belgium and the USA from 2013 to 2014, followed by a sales decline in the UK and USA in 2015.

Conclusion & Recommendation

The analysis reveals important dynamics shaping business performance. Revenue is heavily concentrated among a few top customers, while others show signs of churn due to inactivity or low engagement. Product performance is uneven, with strong contributions from categories like Beverages contrasted by items such as Mishi Kobe Niku, which generate minimal demand. Shipping operations also show mixed results: Federal Shipping excels in speed, whereas United Package handles greater volumes but struggles with delays. Employee productivity remains largely consistent, though certain individuals stand out for faster turnaround times or higher workloads. Sales patterns further underscore the role of seasonality and regional variation, with Belgium and the USA experiencing strong growth while the UK shows slower momentum.

These findings point to clear opportunities for action. Management can reinforce relationships with high-value customers while designing strategies to re-engage those at risk of churn. Reviewing the product portfolio will ensure resources focus on high-performing categories while phasing out weak performers. Shipping reliability can be improved by balancing speed with volume, possibly through a redistribution of orders among carriers. Investing in employee development, guided by best practices from top performers, will help raise efficiency across the team. Finally, planning around seasonal peaks and regional trends will enable better forecasting and more targeted strategies for sustaining long-term growth.