Mathew K AnalyticsLesson 14 · Supply Chain Operations Analytics
SQL vs Pandas for Operational Reporting: Key Differences & Best Practices
In this lesson, we will explore how to answer key supply chain operations questions using both SQL and Python pandas. We will work with real-world datasets…
- CourseSupply Chain Operations Analytics
- Lesson14 of 27
- Video24 min
- FormatJupyter notebook · 21 code cells
What you'll learn
- Understanding the Data in Supply Chain Operations
- Beginner Task 1: Counting Orders by Country with SQL (as a reference)
- Beginner Task 2: Filtering Records (WHERE Clause Equivalent)
- Beginner Task 3: Calculating Total Revenue per Transaction
- Intermediate Task 1: Aggregating Revenue by Month and Country
- Intermediate Task 2: Calculating Average Lead Time using Order Data
- Intermediate Task 3: Combining Supplier and Order Insights
- Advanced Task 1: Detecting Negative Quantities and Returns
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbSQL vs Pandas for Operational Reporting in Supply Chains#
- In this lesson, we will explore how to answer key supply chain operations questions using both SQL and Python pandas.
- We will work with real-world datasets such as retail demand, fulfillment, and supplier performance.
- You will learn how to translate classic operational reporting tasks from SQL to pandas.
- The lesson covers beginner tasks, intermediate analyses, and advanced operational scenarios.
- You will learn best practices, debugging techniques, and common pitfalls for supply chain analytics reporting.
import pandas as pd
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding the Data in Supply Chain Operations#
- Operational supply chain datasets typically include demand, orders, fulfillment, inventory, and supplier information.
- Data is often organized in transaction records, with time-stamped events and quantities.
- It is common to make mistakes such as misinterpreting column meanings, missing date formats, or misaligning joins.
- Accurate reporting depends on correctly understanding each field and how it connects to business operations.
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_retail = pd.read_excel(url, sheet_name='Year 2010-2011')
df_retail['InvoiceDate'] = pd.to_datetime(df_retail['InvoiceDate'])
print(df_retail.shape)
print(df_retail.head(3))
openml_id = 42125
dataset = openml.datasets.get_dataset(openml_id)
df_supplier, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_supplier.shape)
print(df_supplier.head(3))
url_orders = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_orders = pd.read_csv(url_orders)
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'])
print(df_orders.shape)
print(df_orders.head(3))
Beginner Task 1: Counting Orders by Country with SQL (as a reference)#
- In SQL, grouping and counting is common for demand or sales reports.
- SQL example: SELECT Country, COUNT(*) FROM sales GROUP BY Country;
- In pandas, we use groupby and size for the same result.
country_counts = df_retail.groupby('Country').size().sort_values(ascending=False)
print(country_counts.head(10))
Beginner Task 2: Filtering Records (WHERE Clause Equivalent)#
- SQL uses WHERE for conditional filters, such as WHERE Country = 'United Kingdom'.
- In pandas, logical masks or .query are used.
uk_sales = df_retail[df_retail['Country'] == 'United Kingdom']
print(uk_sales.shape)
print(uk_sales.head(2))
Beginner Task 3: Calculating Total Revenue per Transaction#
- SQL uses expressions in SELECT, e.g., Quantity * Price as Revenue.
- In pandas, create a new column as a computed field.
df_retail['Revenue'] = df_retail['Quantity'] * df_retail['Price']
print(df_retail[['Quantity', 'Price', 'Revenue']].head(3))
Intermediate Task 1: Aggregating Revenue by Month and Country#
- SQL often uses GROUP BY EXTRACT(MONTH FROM date), Country.
- In pandas, use groupby with .dt accessor for datetime fields.
df_retail['Month'] = df_retail['InvoiceDate'].dt.month
monthly_country = df_retail.groupby(['Month', 'Country'])['Revenue'].sum().unstack().fillna(0)
print(monthly_country.iloc[:3, :5])
monthly_revenue = df_retail.groupby('Month')['Revenue'].sum()
print(monthly_revenue)
Intermediate Task 2: Calculating Average Lead Time using Order Data#
- SQL typically uses DATEDIFF or TIMESTAMPDIFF on date columns.
- In pandas, subtract datetime columns and calculate mean.
lead_time = (df_orders['order_delivered_customer_date'] - df_orders['order_purchase_timestamp']).dt.days
mean_lead_time = lead_time.mean()
print(f'Average lead time in days: {mean_lead_time:.2f}')
lead_time_valid = lead_time.dropna()
print('Percentage of orders with valid lead times:', len(lead_time_valid) / len(lead_time) * 100)
Intermediate Task 3: Combining Supplier and Order Insights#
- SQL would use JOIN statements to combine purchase data and supplier details.
- In pandas, this is achieved using merge().
# Note: For demonstration, let us assume both DataFrames have a 'customer_id' column for joining
common_customers = set(df_orders['customer_id']).intersection(set(df_supplier['full_name']))
print('Common customer count (as fake join key):', len(common_customers))
Advanced Task 1: Detecting Negative Quantities and Returns#
- In retail operations, negative quantities often indicate product returns.
- SQL: SELECT * FROM sales WHERE Quantity < 0;
- In pandas: sales[sales.Quantity < 0]
returns = df_retail[df_retail['Quantity'] < 0]
print('Number of return transactions:', len(returns))
print(returns[['Invoice', 'Quantity', 'Revenue']].head(3))
Advanced Task 2: Calculating Fill Rate Over Time#
- Fill Rate is a key operational KPI for fulfillment performance.
- Fill Rate = (delivered orders) / (total orders) for a period.
- SQL would GROUP BY time period and SUM CASE WHEN delivered.
- In pandas, use groupby and logical conditions.
df_orders['month'] = df_orders['order_purchase_timestamp'].dt.to_period('M')
delivered = df_orders['order_status'] == 'delivered'
monthly_fill_rate = delivered.groupby(df_orders['month']).mean()
print(monthly_fill_rate.head())
Advanced Task 3: Supplier Cost Efficiency Insight#
- Typically, SQL queries rank suppliers by cost or efficiency.
- In pandas, sort supplier metrics and filter top performers.
if 'current_annual_salary' in df_supplier.columns:
top_cost_suppliers = df_supplier.sort_values('current_annual_salary').head(5)
print(top_cost_suppliers[['full_name', 'current_annual_salary']])
Error Handling 1: Handling Missing Dates for Lead Time Calculation#
- Missing date fields break TIMESTAMPDIFF or subtraction in SQL and pandas.
- Pandas throws errors or NaN values when dates are missing.
missing_delivery = df_orders['order_delivered_customer_date'].isnull().sum()
missing_purchase = df_orders['order_purchase_timestamp'].isnull().sum()
print(f'Missing delivered dates: {missing_delivery}')
print(f'Missing purchase dates: {missing_purchase}')
Error Handling 2: Join Errors and Data Misalignment#
- SQL will fail or produce empty joins when keys are inconsistent.
- In pandas, merge() can create many NaNs and miss expected rows.
- Always check the key uniqueness and types before joining.
unique_order_ids = df_orders['order_id'].nunique()
total_order_rows = len(df_orders)
print(f'Orders: {unique_order_ids} unique order_ids vs {total_order_rows} rows.')
Error Handling 3: Quantity and Lead Time Misinterpretation#
- Negative quantities often mean returns, not demand.
- SQL and pandas both include these in aggregates unless filtered.
- Always clarify business definitions before calculation.
total_quantity = df_retail['Quantity'].sum()
demand_quantity = df_retail[df_retail['Quantity'] > 0]['Quantity'].sum()
returns_quantity = df_retail[df_retail['Quantity'] < 0]['Quantity'].sum()
print(f'Total quantity (including returns): {total_quantity}')
print(f'Demand (positive only): {demand_quantity}')
print(f'Returns (negative only): {returns_quantity}')
Best Practices: Aggregation and KPI Calculation Patterns#
- Always use explicit grouping, clean column names, and conversions for reporting.
- Validate aggregations by checking shapes and sample outputs.
- Time-series operations should use datetime columns with proper frequency.
- KPIs must be defined carefully and checked for degeneracies (NaN, zeros, missing).
kpi = monthly_revenue.max() / monthly_revenue.mean()
print(f'Maximum-to-average monthly revenue ratio: {kpi:.2f}')
Best Practices: Time Series Grouping#
- For operational analytics, group by period using .to_period or .dt accessor.
- Always ensure you group on business-relevant time boundaries (month, week, day).
- Double-check if you are grouping on the correct time column.
daily_orders = df_orders.groupby(df_orders['order_purchase_timestamp'].dt.date).size()
print(daily_orders.head())
Example: End-to-End Operational Metric Lead Time Variability#
- We will use order data to calculate the standard deviation of delivery lead times each month.
- This metric allows an operations manager to detect unstable fulfillment processes.
lead_time = (df_orders['order_delivered_customer_date'] - df_orders['order_purchase_timestamp']).dt.days
df_orders['lead_time'] = lead_time
monthly_lead_time_std = df_orders.groupby('month')['lead_time'].std()
print(monthly_lead_time_std.head())
Summary: SQL vs Pandas in Supply Chain Operational Analytics#
- Both SQL and pandas offer powerful tools for operational reporting.
- SQL excels at batch queries and data warehousing. Pandas enables flexible data cleaning and quick analytics.
- Knowing both approaches lets you choose the right tool for each operational analytics task.
- Want more? Watch our YouTube playlist for live demonstrations and expert tips!
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
23 minLesson 15
Assessing Financial Data Quality: Techniques and Best Practices for Accurate Analysis
12 minLesson 23
Best Practices for Data Storytelling Using Visuals in Bible Teaching | Week 07–08
27 minLesson 54
Best Practices for Market Research Visuals | Python Analytics Tutorial
29 minLesson 59