-- On-time delivery rate by route, last 30 days (PostgreSQL)
SELECT origin_hub, destination_hub,
       COUNT(*) AS delivered,
       ROUND(100.0 * AVG(CASE WHEN delivered_at::date <= promised_date THEN 1 ELSE 0 END), 1) AS on_time_pct
FROM shipments
WHERE status = 'delivered'
  AND delivered_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY origin_hub, destination_hub
HAVING COUNT(*) >= 50
ORDER BY on_time_pct ASC
LIMIT 10;

import pandas as pd

df = pd.read_csv("late_shipments.csv")   # shipment_id, delay_stage, hours_late
summary = (df.groupby("delay_stage")["hours_late"]
             .agg(["count", "mean"]).sort_values("count", ascending=False))
summary["share_%"] = (summary["count"] / summary["count"].sum() * 100).round(1)
print(summary)
# Shows which stage — pickup, hub transfer, last mile — causes most delays

-- Cost and service by carrier, last quarter
SELECT carrier,
       COUNT(*) AS shipments,
       ROUND(SUM(freight_cost) / COUNT(*), 2) AS cost_per_shipment,
       ROUND(100.0 * AVG(CASE WHEN delivered_at::date <= promised_date THEN 1 ELSE 0 END), 1) AS on_time_pct
FROM shipments
WHERE delivered_at >= date_trunc('quarter', CURRENT_DATE) - INTERVAL '3 months'
  AND delivered_at <  date_trunc('quarter', CURRENT_DATE)
GROUP BY carrier
ORDER BY cost_per_shipment;

Truck utilisation in Excel
Columns: trip_id | capacity_kg | loaded_kg | km | cost
Utilisation %:      =loaded_kg/capacity_kg
Cost per kg:        =cost/loaded_kg
Flag low trips:     =IF(utilisation<0.6,"LOW","OK")
Pivot: average utilisation by route and weekday

import pandas as pd

vol = pd.read_csv("daily_volume.csv", parse_dates=["date"])   # date, parcels
vol["weekday"] = vol["date"].dt.day_name()
vol["month"] = vol["date"].dt.month
print(vol.groupby("weekday")["parcels"].mean().round(0).sort_values(ascending=False))
print(vol.groupby("month")["parcels"].mean().round(0))
# Reveals weekly patterns and festival-season peaks for capacity planning

ABC analysis for slotting
1. Rank SKUs by picks in the last 90 days
2. A = top items making up most picks → place nearest packing
3. B = middle group → medium distance
4. C = slow movers → farther zones
Excel: sort by picks, add cumulative % column:
=SUM($C$2:C2)/SUM($C:$C)
Then label A/B/C by cumulative % thresholds you agree with operations.

Logistics KPI definitions
On-time delivery %: delivered on/before promised date ÷ delivered (exclude customer-requested reschedules)
First-attempt success %: delivered on first attempt ÷ shipments attempted
Cost per shipment: total freight cost ÷ shipments
RTO %: returned to origin ÷ shipped
Pick rate: orders picked ÷ picker hours

Weekly Operations Dashboard
Top: Shipments | On-time % | First-attempt % | Cost/shipment | RTO %
Chart 1: On-time % trend by week
Chart 2: Bottom 10 routes by on-time %
Chart 3: Carrier cost vs on-time scatter
Table: Hubs with backlog > threshold
Filters: region, carrier, service type

Portfolio case study: 'Reducing late deliveries'
Data: synthetic shipments table with routes, carriers, timestamps
Questions:
1. Which routes and stages cause most delays?
2. Which carrier gives the best cost-service balance?
3. What three actions would you recommend?
Deliverables: SQL, Power BI dashboard, one-page memo