Mathew K AnalyticsLesson 12 · Supply Chain Operations Analytics
Introduction to SQL for Supply Chain Analysts
In this lesson, we will learn how SQL can help analyze real operational data. We will explore how retailers and manufacturers use SQL queries to answer core…
- CourseSupply Chain Operations Analytics
- Lesson12 of 27
- Video23 min
- FormatJupyter notebook · 21 code cells
What you'll learn
- Understanding Supply Chain Data: What Are We Working With?
- Beginner Example 1: Selecting Columns
- Beginner Example 2: Filtering Data with WHERE
- Beginner Example 3: Simple Aggregation with GROUP BY
- Intermediate Example 1: Filtering Dates for Sales Trends
- Intermediate Example 2: Calculating Revenue per Product
- Intermediate Example 3: Joining Multiple Real Supply Chain Tables
- Advanced Example 1: Window Functions for Supply Chain KPIs
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbIntroduction to SQL for Supply Chain Analysts#
- In this lesson, we will learn how SQL can help analyze real operational data.
- We will explore how retailers and manufacturers use SQL queries to answer core business questions.
- You will learn to manipulate supply chain datasets, extract insights, and avoid common pitfalls.
- By the end, you will know how to use SQL-like operations in Python for supply chain analytics.
import pandas as pd
import sqlite3
import warnings
warnings.filterwarnings('ignore')
Understanding Supply Chain Data: What Are We Working With?#
- Supply chain datasets can represent demand, inventory, supplier performance, or order fulfillment.
- Operational datasets are usually tables, like SQL tables, with rows and columns.
- Each row is a transaction or event, such as a sale, shipment, or delivery.
- Beginners often make mistakes like choosing the wrong keys for joining tables, or ignoring missing data like delivery dates.
- Understanding your data structure is the foundation for correct SQL analysis.
# Loading a real retail demand dataset as our SQL table
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df.shape)
print(df.head(3))
# Initialize an in-memory SQLite database and load the table
conn = sqlite3.connect(':memory:')
df.to_sql('retail', conn, index=False, if_exists='replace')
cursor = conn.cursor()
Beginner Example 1: Selecting Columns#
- Business problem: What are the details of each transaction?
- We will use SQL SELECT to view specific columns like Invoice, StockCode, and Quantity.
- This is like viewing selected fields in an Excel spreadsheet.
query = "SELECT Invoice, StockCode, Quantity FROM retail LIMIT 5;"
result = pd.read_sql(query, conn)
print(result)
Beginner Example 2: Filtering Data with WHERE#
- Business problem: Which transactions happened in France?
- SQL WHERE clauses let us filter records to answer focused questions.
query = "SELECT Invoice, Country, Quantity FROM retail WHERE Country = 'France' LIMIT 5;"
result = pd.read_sql(query, conn)
print(result)
Beginner Example 3: Simple Aggregation with GROUP BY#
- Business problem: How many total items sold per country?
- SQL GROUP BY lets us roll up numbers for a summary view.
query = "SELECT Country, SUM(Quantity) AS Total_Quantity FROM retail GROUP BY Country ORDER BY Total_Quantity DESC;"
result = pd.read_sql(query, conn)
print(result.head(7))
Intermediate Example 1: Filtering Dates for Sales Trends#
- Business problem: How many sales invoices did we issue in December 2010?
- We use SQL WHERE and datetime functions to limit the time range.
query = """
SELECT COUNT(DISTINCT Invoice) AS Dec_Invoices
FROM retail
WHERE strftime('%Y-%m', InvoiceDate) = '2010-12';
"""
result = pd.read_sql(query, conn)
print(result)
Intermediate Example 2: Calculating Revenue per Product#
- Business problem: Which items earned the most revenue?
- Operations teams need to find best sellers by joining Quantity and Price in a SQL calculation.
query = """
SELECT StockCode, Description, SUM(Quantity*Price) AS TotalRevenue
FROM retail
GROUP BY StockCode, Description
ORDER BY TotalRevenue DESC
LIMIT 5;
"""
result = pd.read_sql(query, conn)
print(result)
Intermediate Example 3: Joining Multiple Real Supply Chain Tables#
- Business problem: Can we analyze orders together with supplier info?
- SQL JOINs let us merge data from two tables for a richer view.
# Load another real supply chain dataset: supplier performance
import openml
supplier_data = openml.datasets.get_dataset(42125)
df_sup, _, _, _ = supplier_data.get_data(dataset_format='dataframe')
df_sup_small = df_sup[['full_name', 'department', 'division', 'date_first_hired']].drop_duplicates().head(100)
df_sup_small.to_sql('suppliers', conn, index=False, if_exists='replace')
query = """
SELECT r.Invoice, r.StockCode, s.department, s.division
FROM retail r
LEFT JOIN suppliers s
ON r.Country = s.division
LIMIT 7;
"""
result = pd.read_sql(query, conn)
print(result)
Advanced Example 1: Window Functions for Supply Chain KPIs#
- Business problem: What is the running total revenue by invoice date?
- Advanced SQL window functions help view trends over timecritical for operations.
query = """
SELECT InvoiceDate,
SUM(Quantity*Price) AS DailyRevenue,
SUM(SUM(Quantity*Price)) OVER (ORDER BY InvoiceDate) AS RunningTotal
FROM retail
GROUP BY InvoiceDate
ORDER BY InvoiceDate
LIMIT 7;
"""
try:
result = pd.read_sql(query, conn)
print(result)
except Exception as e:
print('SQLite window functions are only available in some versions. Error:', e)
Advanced Example 2: Detecting Anomalies with SQL#
- Business problem: Find unusually large orders for risk management.
- Using SQL, we can identify transactions where Quantity is much larger than normal.
query = """
SELECT Invoice, StockCode, Quantity
FROM retail
WHERE Quantity > (
SELECT AVG(Quantity) + 3* (SELECT AVG(ABS(Quantity-(SELECT AVG(Quantity) FROM retail))) FROM retail)
FROM retail
)
ORDER BY Quantity DESC
LIMIT 5;
"""
result = pd.read_sql(query, conn)
print(result)
Advanced Example 3: Subqueries for Complex Metrics#
- Business problem: Find items only sold in the top 3 countries.
- Subqueries help us answer niche business questions that require multiple calculations.
query = """
SELECT DISTINCT StockCode, Description
FROM retail
WHERE Country IN (
SELECT Country FROM retail GROUP BY Country ORDER BY SUM(Quantity) DESC LIMIT 3
)
LIMIT 10;
"""
result = pd.read_sql(query, conn)
print(result)
Error Handling: Missing Data in Operations SQL#
- Business problem: What happens if an order is missing a delivery date?
- Missing values are common in real operations, and SQL lets us filter or flag these.
# Find missing delivery dates in another operations dataset
o_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_orders = pd.read_csv(o_url)
df_orders['order_purchase_timestamp'] = pd.to_datetime(df_orders['order_purchase_timestamp'])
df_orders['order_delivered_customer_date'] = pd.to_datetime(df_orders['order_delivered_customer_date'])
df_orders.to_sql('orders', conn, index=False, if_exists='replace')
query = "SELECT COUNT(*) AS MissingDeliveries FROM orders WHERE order_delivered_customer_date IS NULL;"
result = pd.read_sql(query, conn)
print(result)
Error Handling: Incorrect SQL Joins#
- Business problem: What can go wrong if you join tables on the wrong keys?
- Inconsistent IDs or field names often lead to missing or duplicated data.
# Intentionally join on unrelated fields (bad example)
query = "SELECT r.Invoice, o.order_id FROM retail r INNER JOIN orders o ON r.Invoice = o.order_id LIMIT 5;"
result = pd.read_sql(query, conn)
print(result)
Error Handling: Misinterpreting Lead Times or Quantities#
- Business problem: Negative or missing quantities can break inventory analysis.
- We should use SQL to filter out or correct such problematic records.
query = "SELECT COUNT(*) as NegativeQuantities FROM retail WHERE Quantity < 0;"
result = pd.read_sql(query, conn)
print(result)
Best Practices: Aggregation and Grouping in SQL#
- Business problem: Management wants fast summaries, not raw data.
- Use GROUP BY, SUM, COUNT, and AVG for regular supply chain reporting.
query = "SELECT Country, COUNT(DISTINCT Invoice) AS UniqueInvoices, AVG(Quantity) AS AvgItems FROM retail GROUP BY Country;"
result = pd.read_sql(query, conn)
print(result.head(7))
Best Practices: Calculating Fulfillment Time#
- Business problem: Operations teams track delivery times as a core metric.
- Using SQL, we can compute the average time from order to delivery.
query = """
SELECT AVG(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp)) AS AvgFulfillmentDays
FROM orders
WHERE order_delivered_customer_date IS NOT NULL;
"""
result = pd.read_sql(query, conn)
print(result)
Tiny End-to-End Problem: From Raw Data to Insight#
- Business problem: Which country delivered the highest average revenue per order in 2011?
- Let us combine filtering, aggregation, and SQL arithmetic for a complete analytics cycle.
query = """
SELECT Country,
SUM(Quantity*Price)*1.0/COUNT(DISTINCT Invoice) AS AvgOrderRevenue
FROM retail
WHERE strftime('%Y', InvoiceDate) = '2011'
GROUP BY Country
ORDER BY AvgOrderRevenue DESC
LIMIT 5;
"""
result = pd.read_sql(query, conn)
print(result)
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
