# Photonics Lot Yield and Test Results SQL Query Generator

> Source: https://netray.co/tools/photonics-lot-yield-and-test-results-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate SQL queries for lot-level first-pass yield, fallout by test step and a defect Pareto using your own table and column names.

## Category

developer

## Industry

Photonics and Optics

## Audience

- Quality engineer
- Production engineer
- ERP developer
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Test results table

**Key:** results_table

**Type:** text

**Default:** test_results

**Help:** One row per unit per test step

#### Lot column

**Key:** lot_col

**Type:** text

**Default:** lot_number

#### Unit or serial column

**Key:** unit_col

**Type:** text

**Default:** serial_number

#### Test step column

**Key:** step_col

**Type:** text

**Default:** test_step

#### Result column

**Key:** result_col

**Type:** text

**Default:** result

**Help:** Values assumed to be PASS or FAIL

#### Defect code column

**Key:** defect_col

**Type:** text

**Default:** defect_code

#### Test date column

**Key:** date_col

**Type:** text

**Default:** tested_at

#### From date

**Key:** from_date

**Type:** date

**Default:** 2026-09-01

#### To date

**Key:** to_date

**Type:** date

**Default:** 2026-09-30

**Blocks:**

#### 1. First-pass yield by lot

**Body:** -- Units whose FIRST recorded result per step was PASS across all steps
SELECT
  {{lot_col}} AS lot,
  COUNT(DISTINCT {{unit_col}}) AS units_tested,
  COUNT(DISTINCT CASE WHEN {{result_col}} = 'FAIL' THEN {{unit_col}} END) AS units_with_a_fail,
  100.0 * (COUNT(DISTINCT {{unit_col}})
    - COUNT(DISTINCT CASE WHEN {{result_col}} = 'FAIL' THEN {{unit_col}} END))
    / NULLIF(COUNT(DISTINCT {{unit_col}}), 0) AS first_pass_yield_pct
FROM {{results_table}}
WHERE {{date_col}} >= '{{from_date}}'
  AND {{date_col}} < DATEADD(day, 1, '{{to_date}}')
GROUP BY {{lot_col}}
ORDER BY first_pass_yield_pct ASC;

#### 2. Fallout by test step

**Body:** SELECT
  {{step_col}} AS test_step,
  COUNT(*) AS tests_run,
  SUM(CASE WHEN {{result_col}} = 'FAIL' THEN 1 ELSE 0 END) AS fails,
  100.0 * SUM(CASE WHEN {{result_col}} = 'FAIL' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS fail_rate_pct
FROM {{results_table}}
WHERE {{date_col}} >= '{{from_date}}'
  AND {{date_col}} < DATEADD(day, 1, '{{to_date}}')
GROUP BY {{step_col}}
ORDER BY fails DESC;

#### 3. Defect Pareto with cumulative percent

**Body:** WITH d AS (
  SELECT {{defect_col}} AS defect, COUNT(*) AS n
  FROM {{results_table}}
  WHERE {{result_col}} = 'FAIL'
    AND {{defect_col}} IS NOT NULL
    AND {{date_col}} >= '{{from_date}}'
    AND {{date_col}} < DATEADD(day, 1, '{{to_date}}')
  GROUP BY {{defect_col}}
)
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;

**Notes:** Table and column names here are generic placeholders, not a specific ERP or MES schema, so map them to your own. DATEADD and the window syntax shown are SQL Server style, so adapt date functions for Oracle, PostgreSQL or MySQL. First-pass yield here counts a unit as failed if any recorded fail exists, so retests should be flagged with an attempt column if you need strict first-attempt logic. Run on a read-only replica first.

## Content

**Intro:** This generator produces three SQL queries that most photonics and electronics quality teams need every week: first-pass yield by lot, fallout by test step, and a defect Pareto. You type in your own table and column names, copy the SQL, and run it against a read replica of your MES, test database or ERP quality tables.

**Sections:**

#### How to use it

**Body:** Enter the name of the table that holds one row per unit per test step, then the lot, serial, step, result, defect and date columns. Set the date window and copy each query. The queries assume results are stored as PASS or FAIL text, so edit the comparison values if your system uses codes. Always run new SQL on a reporting copy or with read-only credentials before pointing it at production.

**Bullets:**

- Use a read-only database account
- Map generic column names to your schema
- Adapt DATEADD and window functions to your database
- Add a product or part filter for large databases

#### What the three queries tell you

**Body:** First-pass yield by lot ranks lots from worst to best so you can see which builds need investigation. Fallout by test step shows where in the flow the losses occur, such as power, spectrum, alignment or leak test for modules. The defect Pareto puts failures in order with a cumulative percentage, which usually shows that three or four codes drive most of the loss, and tells you where to focus corrective action.

#### First-pass yield and rolled throughput

**Body:** First-pass yield is units passing with no rework or retest divided by units started. Rolled throughput yield multiplies the first-pass yield of each step, so a flow with five steps at 95 percent each yields about 77 percent, not 95. If your database logs attempts, extend the queries with an attempt number and count only attempt one, so retests do not hide true process loss.

#### Common mistakes

**Body:** Counting rows instead of distinct units inflates fallout when a unit is tested several times. Mixing different part numbers in one yield figure hides product-specific problems. Excluding scrapped units that never reached final test makes yield look better than it is. Make sure date filters use the test date, not the lot creation date, and document your yield definition so reports compare like with like.

**Cta:** If you want these yield and fallout questions answered in plain language directly from your ERP, MES and test data, we can build a private, read-only assistant on your own infrastructure. Reach out to discuss it.

**Faq:**

#### Will these queries work on my ERP or MES schema?

**Answer:** They use generic names, so you must map the table and column names to your own schema. The logic works with any database that stores one test result per unit and step. Date and window functions may need adjusting for Oracle, PostgreSQL or MySQL.

#### How is first-pass yield calculated here?

**Answer:** A unit counts as a failure if any FAIL result exists for it in the date window, and yield is units without a fail divided by distinct units tested. If you retest failures, add an attempt column and count only first attempts for a stricter definition.

#### Is it safe to run generated SQL on production?

**Answer:** The queries only read data, but heavy aggregations can slow a busy system. Run them on a replica or during quiet hours, with a read-only account and a limited date range. Review the SQL before execution, as you would any script.

#### Why do I need a defect Pareto?

**Answer:** A Pareto ranks defect causes by count, with a cumulative percentage line. It usually shows that a small number of causes create most of the loss, so you can put corrective actions where they pay back most, rather than spreading effort across every defect code.

## Search terms

first pass yield sql query, lot yield report sql, test fallout by step sql, defect pareto sql, manufacturing yield query generator, mes test results sql, photonics yield report, sql rolled throughput yield

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json