Inspection Reject Rate SQL Query Generator

SELECT l.vendor_id AS supplier,
       COUNT(*) AS lots_inspected,
       SUM(CASE WHEN l.usage_decision = 'R' THEN 1 ELSE 0 END) AS lots_rejected,
       100.0 * SUM(CASE WHEN l.usage_decision = 'R' THEN 1 ELSE 0 END) / COUNT(*) AS reject_rate_pct
FROM inspection_lot l
WHERE l.inspection_date >= '2026-01-01'
  AND l.inspection_date <= '2026-09-30'
GROUP BY l.vendor_id
ORDER BY reject_rate_pct DESC;

SELECT r.characteristic AS characteristic,
       COUNT(*) AS failures
FROM inspection_result r
JOIN inspection_lot l ON l.lot_id = r.lot_id
WHERE r.result_status = 'F'
  AND l.inspection_date >= '2026-01-01'
  AND l.inspection_date <= '2026-09-30'
GROUP BY r.characteristic
ORDER BY failures DESC;

SELECT l.vendor_id AS supplier,
       r.characteristic AS characteristic,
       COUNT(*) AS failures
FROM inspection_result r
JOIN inspection_lot l ON l.lot_id = r.lot_id
WHERE r.result_status = 'F'
  AND l.inspection_date >= '2026-01-01'
  AND l.inspection_date <= '2026-09-30'
GROUP BY l.vendor_id, r.characteristic
HAVING COUNT(*) >= 3
ORDER BY failures DESC;