🚀 SQL Project Series #19

Logistics & Shipment Analytics 📦

Analyze shipments, warehouses, delivery performance, customers, and transportation data using SQL to optimize supply chain operations and improve delivery efficiency.

🎯 Business Objectives
✅ Track shipments
✅ Monitor delivery performance
✅ Analyze warehouse operations
✅ Measure transportation efficiency
✅ Identify delayed deliveries
✅ Optimize shipping costs
✅ Analyze customer orders
✅ Build logistics dashboards

📂 Step 1: Create Database
CREATE DATABASE logistics_db;
USE logistics_db;

📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50)
);

📂 Step 3: Create Warehouses Table
CREATE TABLE warehouses (
warehouse_id INT PRIMARY KEY,
warehouse_name VARCHAR(100),
city VARCHAR(50)
);

📂 Step 4: Create Shipments Table
CREATE TABLE shipments (
shipment_id INT PRIMARY KEY,
customer_id INT,
warehouse_id INT,
shipment_date DATE,
delivery_date DATE,
shipping_cost DECIMAL(10,2),
shipment_status VARCHAR(30),
delivery_partner VARCHAR(100),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

📂 Step 5: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai'),
(2,'Priya Verma','Delhi'),
(3,'Amit Patel','Pune'),
(4,'Sneha Joshi','Bangalore'),
(5,'Rohan Gupta','Hyderabad');

📂 Step 6: Insert Sample Warehouses
INSERT INTO warehouses VALUES
(101,'Mumbai Warehouse','Mumbai'),
(102,'Delhi Warehouse','Delhi'),
(103,'Pune Warehouse','Pune'),
(104,'Bangalore Warehouse','Bangalore');

📂 Step 7: Insert Sample Shipments
INSERT INTO shipments VALUES
(1001,1,101,'2025-01-05','2025-01-07',450,'Delivered','BlueDart'),
(1002,2,102,'2025-01-06','2025-01-09',620,'Delivered','DTDC'),
(1003,3,103,'2025-01-07','2025-01-11',780,'Delayed','Delhivery'),
(1004,4,104,'2025-01-08','2025-01-10',390,'Delivered','XpressBees'),
(1005,5,101,'2025-01-09','2025-01-14',950,'Delayed','BlueDart');

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Shipments
📈 Delivered Shipments
📈 Delayed Shipments
📈 Delivery Success Rate
📈 Average Delivery Time
📈 Total Shipping Cost
📈 Shipping Cost by Warehouse
📈 Shipping Cost by Delivery Partner
📈 Shipments by City
📈 Shipments by Warehouse
📈 Delivery Partner Performance
📈 Average Delivery Time by Partner
📈 Warehouse Utilization
📈 Peak Shipment Days
📈 Monthly Shipment Trend
📈 Customer-wise Shipments
📈 On-Time Delivery Rate
📈 Delayed Delivery Analysis
📈 Cost per Shipment
📈 Executive Logistics Dashboard

🎯 This project reflects real-world SQL analysis
Performed by Supply Chain Analysts, Logistics Analysts, Operations Analysts, and Business Intelligence professionals at e-commerce, courier, manufacturing, and retail companies to optimize delivery performance, reduce costs, and improve customer satisfaction.

💡 Double Tap ❤️ For More