@gudanglifehack: SQL Project Series #19 Logistics & Shipment Analytics Analyze shipments, warehouses, delivery performance, customers, a…

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.