Project Objective

The goal of this project is to analyse and manage warehousing and order data using MySQL, demonstrating proficiency in database querying, data aggregation, and advanced SQL techniques. The project extracts actionable insights and solves real-world problems related to warehouse management and order processing.

Dataset Description

This analysis involves two tables:

1. Warehouse Table

Column Name Description
warehouse_id Unique identifier for each warehouse
warehouse_alias Alias or name of the warehouse
maximum_capacity Maximum storage capacity
employee_total Number of employees in the warehouse
state Location or state of the warehouse

2. Orders Table

Column Name Description
order_id Unique identifier for each order
customer_id Identifier for the customer
warehouse_id ID of the warehouse fulfilling the order
order_date Date the order was placed
shipper_date Date the order was shipped

Tasks and SQL Queries

1. Data Analysis with JOINS

1.1 Find the total number of orders handled by each Warehouse

SELECT
    WW.warehouse_id,
    WW.warehouse_alias,
    COUNT(WO.order_id) `Total Number of Orders`
FROM warehousewarehouse WW
LEFT JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY 
    WW.warehouse_id,
    WW.warehouse_alias;

1.2 List all warehouses with the total number of employees and orders processed

SELECT 
    WW.warehouse_id,
    WW.warehouse_alias,
    WW.employee_total  `Total Number of Employees`,
    COUNT(WO.order_id) `Total Number of Orders`
FROM warehousewarehouse WW
LEFT JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY  
    WW.warehouse_id,
    WW.warehouse_alias,
    WW.employee_total;

1.3 Retrieve detailed order information, including the Warehouse’s alias and state.

SELECT 
    WO.order_id,
    WO.customer_id,
    WO.order_date,
    WW.warehouse_id, 
    WW.warehouse_alias, 
    MAX(WO.order_date) `Recent Order Date`
FROM warehousewarehouse WW 
LEFT JOIN warehouseorders WO 
ON WW.warehouse_id = WO.warehouse_id
GROUP BY 
    WW.warehouse_id, 
    WW.warehouse_alias;

2. Data Insights with Functions

2.1 Calculate the average number of employees across all warehouses

SELECT
  AVG(employee_total) `Average Employees`
FROM warehousewarehouse;

2.2 Identify the warehouses with the highest capacity

SELECT 
    warehouse_id,
    warehouse_alias,
    maximum_capacity
FROM warehousewarehouse
WHERE maximum_capacity = (
                            SELECT MAX(maximum_capacity) 
                            FROM warehousewarehouse
                        );

2.3 Determine the most recent order date for each warehouse

SELECT 
      WW.warehouse_id, 
    WW.warehouse_alias, 
    MAX(WO.order_date) `Recent Order Date`
FROM warehousewarehouse WW 
LEFT JOIN warehouseorders WO 
ON WW.warehouse_id = WO.warehouse_id
GROUP BY 
    WW.warehouse_id, 
    WW.warehouse_alias;

3. Logical Filtering

3.1 List all Orders placed and shipped on the Same Day

SELECT 
    order_id,
    customer_id,
    warehouse_id,
    order_date,
    shipper_date
FROM warehouseorders 
WHERE order_date = shipper_date;

3.2 Find warehouses with a capacity above 450 and fewer than 20 employees

SELECT 
    warehouse_id, 
    warehouse_alias, 
    maximum_capacity, 
    employee_total
FROM warehousewarehouse
WHERE maximum_capacity > 450 AND employee_total < 20;

3.3 Identify orders placed in June 2019

SELECT
    order_id,
    customer_id,
    warehouse_id,
    order_date,
    shipper_date
FROM warehouseorders
WHERE YEAR(order_date) = 2019 AND MONTH(order_date) = 6
ORDER BY warehouse_id;

4. Advanced Queries

4.1 Find the top 3 warehouses by order count, along with their capacity and state

SELECT
    WW.warehouse_id, 
    WW.warehouse_alias, 
    COUNT(WO.order_id) `Order Count`
FROM warehousewarehouse WW
JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY
    WW.warehouse_id, 
    WW.warehouse_alias
ORDER BY `Order Count` DESC
LIMIT 3;

4.2 List orders with warehouse details where the warehouse has more than 10 employees and is located in ‘MI’

SELECT 
    WO.order_id, 
    WO.customer_id, 
    WO.order_date,
    WO.shipper_date, 
    WW.warehouse_id, 
    WW.warehouse_alias, 
    WW.state, 
    WW.employee_total
FROM warehouseorders WO
JOIN warehousewarehouse WW 
ON WO.warehouse_id = WW.warehouse_id
WHERE WW.employee_total > 10 AND WW.state = 'MI';

4.3 Identify warehouses with zero orders processed on 26th December 2019

SELECT 
    WW.warehouse_id, 
    WW.warehouse_alias, 
    WW.maximum_capacity, 
    WW.state,
    WO.order_date
FROM warehousewarehouse WW
LEFT JOIN warehouseorders WO 
ON WW.warehouse_id = WO.warehouse_id 
    AND DATE(WO.order_date) = '2019-12-26'
WHERE WO.order_id IS NULL;

Key Insights and COnclusions

1, Order: Analysis of warehouses with the highest and lowest order counts.

  1. Resource Efficiency: Warehouses with minimal employees but high capacities.

  2. Order Timeliness: Identifying orders processed and shipped on the same day.

  3. Performance Metrics: Highlighting top warehouses based on order count and capacity.

Future Recommendations

  1. Optimise resources in underperforming warehouses.

  2. Ensure timely order fulfilment by addressing delays.

  3. Evaluate workforce allocation in warehouses with low employee-to-capacity

Contact If you have questions or suggestions, feel free to reach out to [ or ].