Sahra Logistics warehousing and transport

SELECT c.carrier_name, c.mode,
       count(*) AS delivered,
       round(100.0 * count(*) FILTER (WHERE s.delivered_at::date <= s.promised_date) / count(*), 1) AS on_time_pct
FROM shipments s
JOIN carriers c ON c.carrier_id = s.carrier_id
WHERE s.status = 'delivered'
GROUP BY c.carrier_name, c.mode
ORDER BY on_time_pct DESC;

SELECT status, count(*) AS shipments, count(*) FILTER (WHERE delivered_at IS NULL) AS without_delivery_time
FROM shipments
GROUP BY status
ORDER BY shipments DESC;

SELECT w.warehouse_code, p.sku, p.product_name, i.on_hand, i.allocated, p.reorder_level
FROM inventory_snapshots i
JOIN products p ON p.product_id = i.product_id
JOIN warehouses w ON w.warehouse_id = i.warehouse_id
WHERE i.snapshot_date = DATE '2025-12-31'
  AND p.is_active
  AND i.on_hand - i.allocated < p.reorder_level
ORDER BY i.on_hand - i.allocated - p.reorder_level;

SELECT warehouse_id, product_id, sum(quantity) AS on_hand
FROM inventory_movements
WHERE movement_date <= DATE '2025-06-30'
GROUP BY warehouse_id, product_id
ORDER BY warehouse_id, product_id
LIMIT 20;

SELECT su.supplier_name,
       count(*) AS received_orders,
       round(avg(po.received_on - po.ordered_on), 1) AS avg_lead_days,
       count(*) FILTER (WHERE po.received_on > po.expected_on) AS late_orders
FROM purchase_orders po
JOIN suppliers su ON su.supplier_id = po.supplier_id
WHERE po.received_on IS NOT NULL
GROUP BY su.supplier_name
ORDER BY late_orders DESC;