LOGISTICS

SELECT carrier, ROUND(AVG(EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 3600), 1) AS avg_hours,
  COUNT(*) AS deliveries
FROM shipments
WHERE shipped_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND delivered_at IS NOT NULL
GROUP BY carrier
ORDER BY avg_hours;

SELECT w.name AS warehouse, p.name AS product, i.quantity AS current_stock, i.reorder_point,
  i.reorder_point - i.quantity AS shortfall
FROM inventory i
JOIN warehouses w ON i.warehouse_id = w.id
JOIN products p ON i.product_id = p.id
WHERE i.quantity < i.reorder_point
ORDER BY shortfall DESC;

SELECT ROUND(
  COUNT(CASE WHEN delivered_at <= estimated_delivery THEN 1 END)::float / COUNT(*) * 100, 1
) AS on_time_pct,
COUNT(*) AS total_shipments
FROM shipments
WHERE shipped_at >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND shipped_at < DATE_TRUNC('quarter', CURRENT_DATE)
  AND delivered_at IS NOT NULL;