Photonics Lot Yield and Test Results SQL Query Generator

-- Units whose FIRST recorded result per step was PASS across all steps
SELECT
  lot_number AS lot,
  COUNT(DISTINCT serial_number) AS units_tested,
  COUNT(DISTINCT CASE WHEN result = 'FAIL' THEN serial_number END) AS units_with_a_fail,
  100.0 * (COUNT(DISTINCT serial_number)
    - COUNT(DISTINCT CASE WHEN result = 'FAIL' THEN serial_number END))
    / NULLIF(COUNT(DISTINCT serial_number), 0) AS first_pass_yield_pct
FROM test_results
WHERE tested_at >= '2026-09-01'
  AND tested_at < DATEADD(day, 1, '2026-09-30')
GROUP BY lot_number
ORDER BY first_pass_yield_pct ASC;

SELECT
  test_step AS test_step,
  COUNT(*) AS tests_run,
  SUM(CASE WHEN result = 'FAIL' THEN 1 ELSE 0 END) AS fails,
  100.0 * SUM(CASE WHEN result = 'FAIL' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS fail_rate_pct
FROM test_results
WHERE tested_at >= '2026-09-01'
  AND tested_at < DATEADD(day, 1, '2026-09-30')
GROUP BY test_step
ORDER BY fails DESC;

WITH d AS (
  SELECT defect_code AS defect, COUNT(*) AS n
  FROM test_results
  WHERE result = 'FAIL'
    AND defect_code IS NOT NULL
    AND tested_at >= '2026-09-01'
    AND tested_at < DATEADD(day, 1, '2026-09-30')
  GROUP BY defect_code
)
SELECT
  defect,
  n,
  100.0 * n / SUM(n) OVER () AS pct,
  100.0 * SUM(n) OVER (ORDER BY n DESC ROWS UNBOUNDED PRECEDING) / SUM(n) OVER () AS cumulative_pct
FROM d
ORDER BY n DESC;