SQL Project: US Candy Distributor

Overview

This SQL project is focused on analyzing sales and geospatial shipment data for a national candy distributor in the United States. The dataset includes information on customer and factory locations, sales orders, goals, and product details. The project will involve analyzing factory-to-customer shipping efficiency, product margins, and ways to optimize shipping routes.

Recommended Analysis

• Most Efficient Factory-to-Customer Shipping Routes: Identify which shipping routes from factories to customers are the most efficient based on travel distances and time.
• Least Efficient Factory-to-Customer Shipping Routes: Find out which shipping routes are underperforming and should be optimized.
• Best Product Margins: Identify which product lines yield the highest margins (based on sales, cost, and profit).
• Factory Optimization: Recommend which product lines should be moved to different factories to optimize shipping routes.

Data Structure

The project involves analyzing data from multiple tables. Below are the tables and their columns used in the analysis:

Tables and Columns

1. Candy_Factories

This table contains information about each candy factory, including its name and geospatial location (latitude and longitude).

        CREATE TABLE Candy_Factories (
            Factory VARCHAR(100) PRIMARY KEY,
            Latitude DECIMAL(9, 6),
            Longitude DECIMAL(9, 6)
        );
    

2. Candy_Products

This table provides product details, including division, product name, and pricing information, as well as the associated factory.

        CREATE TABLE Candy_Products (
            Division VARCHAR(50),
            Product_Name VARCHAR(50),
            Factory VARCHAR(100),
            Product_ID VARCHAR(50) PRIMARY KEY,
            Unit_Price DECIMAL(10, 2),
            Unit_Cost DECIMAL(10, 2),
            CONSTRAINT FK_Products_Division FOREIGN KEY (Division) REFERENCES Candy_Targets (Division),
            CONSTRAINT FK_Products_Factory FOREIGN KEY (Factory) REFERENCES Candy_Factories (Factory)
        );
    

3. Candy_Sales

The sales table contains transactional data for each order, including order date, ship date, customer location, product ID, and sales figures.

        CREATE TABLE Candy_Sales (
            Row_ID INT PRIMARY KEY,
            Order_ID VARCHAR(50),
            Order_Date DATE,
            Ship_Date DATE,
            Ship_Mode VARCHAR(50),
            Customer_ID BIGINT,
            Country_Region VARCHAR(100),
            City VARCHAR(100),
            State_Province VARCHAR(100),
            Postal_Code VARCHAR(20),
            Division VARCHAR(50),
            Region VARCHAR(50),
            Product_ID VARCHAR(50),
            Product_Name VARCHAR(255),
            Sales DECIMAL(15, 2),
            Units INT,
            Gross_Profit DECIMAL(15, 2),
            Cost DECIMAL(15, 2),
            CONSTRAINT FK_Sales_Product_ID FOREIGN KEY (Product_ID) REFERENCES Candy_Products (Product_ID)
        );
    

4. Candy_Targets

The targets table defines sales targets for each product division.

        CREATE TABLE Candy_Targets (
            Division VARCHAR(50) PRIMARY KEY,
            Target SMALLINT
        );
    

5. US_zips

This table provides geospatial data for US zip codes, which is crucial for calculating distances and optimizing shipping routes.

        CREATE TABLE US_zips (
            zip VARCHAR(20) PRIMARY KEY,
            lat DECIMAL(18, 10),
            lng DECIMAL(18, 10),
            city VARCHAR(50),
            state_id VARCHAR(50),
            state_name VARCHAR(50),
            zcta VARCHAR(50),
            parent_zcta VARCHAR(1),
            population INT,
            density DECIMAL(10, 2),
            county_fips INT,
            county_name VARCHAR(50),
            county_weights VARCHAR(100),
            county_names_all VARCHAR(100),
            county_fips_all VARCHAR(50),
            imprecise VARCHAR(50),
            military VARCHAR(50),
            timezone VARCHAR(50)
        );
    

Project Workflow

1. Data Import and Preparation:
   • Load data into the Candy_Factories, Candy_Products, Candy_Sales, Candy_Targets, and US_zips tables.
   • Clean and validate the data to ensure accuracy.
2. Factory-to-Customer Shipping Routes Analysis:
   • Calculate the geospatial distance between each factory and customer location using latitudes and longitudes from the Candy_Factories and US_zips tables.
   • Evaluate the efficiency of these routes based on delivery times and distances.
3. Product Margin Analysis:
   • Calculate product margins for each product using the formula: Margin = (Sales - Cost) / Sales.
   • Identify product lines with the highest margins and investigate factors contributing to their success.
4. Factory Optimization for Shipping Routes:
   • Analyze the shipping routes and identify potential inefficiencies.
   • Recommend factory transfers for specific product lines to improve route efficiency.
5. Reporting and Visualization:
   • Generate reports and visualizations on the analysis results.
   • Use pivot tables to summarize key findings.
   • Visualize efficient and inefficient shipping routes on a map.

Sample Queries

1. Retrieve All Products from a Specific Factory

        SELECT Product_Name, Unit_Price, Unit_Cost
        FROM Candy_Products
        WHERE Factory = 'Secret Factory';
    

2. Calculate Product Margins

        SELECT  Product_Name,
        (Unit_Price - Unit_Cost) AS Margin
        FROM  Candy_Products;
    

3. Get Total Sales and Units Sold by Division

        SELECT Division, 
        SUM(Sales) AS Total_Sales, 
        SUM(Units) AS Total_Units
        FROM Candy_Sales
        GROUP BY Division;
    

4. Find Top 5 Cities by Total Sales

        SELECT top 5 City, 
        SUM(Sales) AS Total_Sales
        FROM Candy_Sales
        GROUP BY City
        ORDER BY Total_Sales DESC;
    

5. Get Factory-Wise Product Count

        SELECT Factory, 
        COUNT(Product_ID) AS Product_Count
        FROM Candy_Products
        GROUP BY Factory
        ORDER BY Product_Count DESC;
    

6. Find Products with Maximum Price Difference Between Units

        SELECT Product_Name,MAX(Unit_Price - Unit_Cost) AS Max_Price_Difference
        FROM Candy_Products
        GROUP BY Product_Name
        ORDER BY Max_Price_Difference DESC;
    

7. Identify Orders with Late Shipments

        SELECT Order_ID, DATEDIFF(DAY, Order_Date, Ship_Date) AS Days_to_Ship,
        CASE WHEN DATEDIFF(DAY, Order_Date, Ship_Date) > 7 THEN 'Late'
        ELSE 'On Time' END AS Shipment_Status
        FROM Candy_Sales
        WHERE Ship_Date IS NOT NULL;