NCR Pareto and Repeat Offender SQL Generator

SELECT
  defect_code AS defect,
  COUNT(*) AS ncr_count,
  SUM(reject_qty) AS rejected_qty,
  ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC, defect_code) / SUM(COUNT(*)) OVER (), 1) AS cumulative_pct
FROM quality_ncr
WHERE ncr_date >= DATE '2026-01-01'
GROUP BY defect_code
ORDER BY ncr_count DESC, defect_code;

SELECT
  supplier_id AS supplier,
  COUNT(*) AS ncr_count,
  COUNT(DISTINCT part_number) AS distinct_parts,
  SUM(reject_qty) AS rejected_qty
FROM quality_ncr
WHERE ncr_date >= DATE '2026-01-01'
GROUP BY supplier_id
HAVING COUNT(*) >= 3
ORDER BY ncr_count DESC;

SELECT
  part_number AS part,
  defect_code AS defect,
  COUNT(*) AS ncr_count
FROM quality_ncr
WHERE ncr_date >= DATE '2026-01-01'
GROUP BY part_number, defect_code
HAVING COUNT(*) >= 3
ORDER BY ncr_count DESC, part_number;