Analyzing Supply Chain Data with SQL Queries
In the logistics and supply chain industry, data management and analysis play a critical role in optimizing operations and reducing costs. The following SQL queries demonstrate how to extract valuable insights from a supply chain database, which includes tables like order_list, warehouse_capacities, product_percost, freightrates, and others.
This article explores various analytical aspects, such as identifying popular products, calculating costs, and analyzing trends.
Data source from https://github.com/SavioSal/datasets/blob/master/Supply%20chain%20logisitcs%20problem.xlsx
Note: The process of data cleaning and processing data been done in Microsoft Excel before importing into mysql workbench.
1. Database Creation and Initialization
To begin, we create a new database named supplychain and set it as the active database
2. Customer Order Analysis
To start with, let’s explore the order data to find the heaviest shipment handled by each carrier. This query helps identify which carrier handles the heaviest loads, which can be useful for logistics planning.
To assess customer activity, we calculate the total number of orders placed by each customer. his query ranks customers based on the number of orders, helping identify key clients and target customer segments for marketing efforts.
3. Warehouse and Product Management
We then analyze the relationship between products and warehouses, along with their capacities:
This query provides insight into which products are stored in which warehouses and their respective capacities.
To find warehouses that haven’t reached full capacity, we use:
4. Product Demand and Storage Costs
To identify the most in-demand products, we analyze the Order_list:
This data helps in inventory planning and ensuring popular products are adequately stocked.
We also calculate the total storage cost for each warehouse:
Understanding storage costs aids in budgeting and cost control.
5. Freight Costs and Shipping Analysis
To determine the total shipping time based on transportation day count:
Total Cost Calculation
For a comprehensive cost analysis, we combine storage and freight costs:
Conclusion
These SQL queries provide a comprehensive toolkit for analyzing various aspects of supply chain operations. From understanding customer behavior and managing warehouse capacities to optimizing freight and storage costs, these insights are invaluable for making data-driven decisions and enhancing overall efficiency in supply chain management.
As a data analyst, I am open to work and collaboration on any data analysis projects. If you have a project or need assistance in this field, feel free to reach out!
