Semiconductor Lot Yield and Bin Pareto SQL Query Generator

SELECT product AS product,
       DATE_TRUNC('week', test_time) AS test_week,
       COUNT(*) AS dies_tested,
       SUM(CASE WHEN hard_bin = 1 THEN 1 ELSE 0 END) AS dies_good,
       ROUND(100.0 * SUM(CASE WHEN hard_bin = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS yield_pct
FROM die_test_results
WHERE product = 'MCU-A32'
  AND test_time >= DATE '2026-07-01'
  AND test_time < DATE '2026-10-01'
GROUP BY product, DATE_TRUNC('week', test_time)
ORDER BY test_week;

SELECT hard_bin AS bin,
       COUNT(*) AS die_count,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_fails,
       ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC) / SUM(COUNT(*)) OVER (), 2) AS cumulative_pct
FROM die_test_results
WHERE product = 'MCU-A32'
  AND hard_bin <> 1
  AND test_time >= DATE '2026-07-01'
  AND test_time < DATE '2026-10-01'
GROUP BY hard_bin
ORDER BY die_count DESC;

SELECT lot_id AS lot,
       COUNT(*) AS dies_tested,
       ROUND(100.0 * SUM(CASE WHEN hard_bin = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS yield_pct
FROM die_test_results
WHERE product = 'MCU-A32'
  AND test_time >= DATE '2026-07-01'
  AND test_time < DATE '2026-10-01'
GROUP BY lot_id
HAVING COUNT(*) >= 5000
ORDER BY yield_pct ASC
LIMIT 20;