# Semiconductor Lot Yield and Bin Pareto SQL Query Generator

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

## Short answer

Fill in your table and column names to get ready-to-run SQL for weekly yield, bin Pareto and lowest-yield lots.

## Category

developer

## Industry

Semiconductors

## Audience

- Yield engineer
- Test engineer
- Data engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Die results table

**Key:** results_table

**Type:** text

**Default:** die_test_results

#### Lot column

**Key:** lot_col

**Type:** text

**Default:** lot_id

#### Product column

**Key:** product_col

**Type:** text

**Default:** product

#### Bin column

**Key:** bin_col

**Type:** text

**Default:** hard_bin

#### Test date column

**Key:** date_col

**Type:** text

**Default:** test_time

#### Passing bin value

**Key:** pass_bin

**Type:** number

**Default:** 1

#### Product name

**Key:** product

**Type:** text

**Default:** MCU-A32

#### Start date

**Key:** start_date

**Type:** date

**Default:** 2026-07-01

#### End date (exclusive)

**Key:** end_date

**Type:** date

**Default:** 2026-10-01

#### Minimum dies per lot

**Key:** min_dies

**Type:** number

**Default:** 5000

**Blocks:**

#### Weekly yield by product

**Body:** SELECT {{product_col}} AS product,
       DATE_TRUNC('week', {{date_col}}) AS test_week,
       COUNT(*) AS dies_tested,
       SUM(CASE WHEN {{bin_col}} = {{pass_bin}} THEN 1 ELSE 0 END) AS dies_good,
       ROUND(100.0 * SUM(CASE WHEN {{bin_col}} = {{pass_bin}} THEN 1 ELSE 0 END) / COUNT(*), 2) AS yield_pct
FROM {{results_table}}
WHERE {{product_col}} = '{{product}}'
  AND {{date_col}} >= DATE '{{start_date}}'
  AND {{date_col}} < DATE '{{end_date}}'
GROUP BY {{product_col}}, DATE_TRUNC('week', {{date_col}})
ORDER BY test_week;

#### Bin Pareto (failing bins only)

**Body:** SELECT {{bin_col}} 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 {{results_table}}
WHERE {{product_col}} = '{{product}}'
  AND {{bin_col}} <> {{pass_bin}}
  AND {{date_col}} >= DATE '{{start_date}}'
  AND {{date_col}} < DATE '{{end_date}}'
GROUP BY {{bin_col}}
ORDER BY die_count DESC;

#### Lowest-yield lots

**Body:** SELECT {{lot_col}} AS lot,
       COUNT(*) AS dies_tested,
       ROUND(100.0 * SUM(CASE WHEN {{bin_col}} = {{pass_bin}} THEN 1 ELSE 0 END) / COUNT(*), 2) AS yield_pct
FROM {{results_table}}
WHERE {{product_col}} = '{{product}}'
  AND {{date_col}} >= DATE '{{start_date}}'
  AND {{date_col}} < DATE '{{end_date}}'
GROUP BY {{lot_col}}
HAVING COUNT(*) >= {{min_dies}}
ORDER BY yield_pct ASC
LIMIT 20;

**Notes:** Table and column names are generic placeholders for your own schema; real test data systems differ widely. The syntax targets PostgreSQL (DATE_TRUNC, LIMIT). For SQL Server use DATEADD/DATEDIFF and TOP, for Oracle use TRUNC(date, 'IW') and FETCH FIRST. Retests and multiple probe passes can double count dies, so filter to the final test pass first.

## Content

**Intro:** This generator writes three SQL queries every yield engineer ends up writing: weekly yield by product, a bin Pareto of failing bins, and a list of the lowest-yield lots. You enter your own table and column names, and the queries update instantly.

**Sections:**

#### How to use it

**Body:** Enter the name of your die level results table and the columns for lot, product, bin and test time. Set the passing bin, the product and the date range, then copy the queries into your SQL client. If your test data is stored per wafer rather than per die, adapt the counts to use die count columns and sum them instead of counting rows.

**Bullets:**

- Use the exclusive end date to avoid double counting boundary days
- Set a minimum die count to hide tiny lots in the worst-lot list
- Run the weekly trend first, then drill into the Pareto

#### The yield and Pareto logic

**Body:** Yield here is good dies divided by dies tested, expressed as a percentage. The Pareto query counts failing dies by bin, then adds a running cumulative percentage so you can see which two or three bins explain most of the loss. This is the standard first step before chasing a root cause with wafer maps or parametric data.

#### Retests and probe passes

**Body:** Many flows probe a wafer more than once. If your table keeps every pass, a raw count mixes first-pass and retest results, and yield looks wrong. Filter to the last pass for each die, or keep separate first-pass and final-yield views. Also confirm whether bin numbers are hard bins or software bins, since they answer different questions.

#### Common mistakes

**Body:** Dividing by wafers instead of dies, mixing products with different die sizes, and averaging lot yields without weighting by die count all distort results. Use total good divided by total tested across the period. Check the time zone of the test time column when grouping by week.

**Cta:** If you want yield questions answered in plain language from your test database or ERP, Netray can set this up on-prem with read-only access.

**Faq:**

#### Will this work with my test database schema?

**Answer:** Only after you set your own table and column names. The names are generic placeholders. If your data is per wafer or per bin summary, you will need to adjust the counting logic.

#### Which SQL dialect is generated?

**Answer:** PostgreSQL style syntax. For SQL Server or Oracle, swap the week truncation and row limit functions as listed in the notes.

#### Why use a minimum die count for lots?

**Answer:** Small lots can show extreme yields by chance. A minimum die count keeps the worst-lot list focused on lots with enough data to be meaningful.

#### Can I use this for final test or OSAT data?

**Answer:** Yes, if you have a unit level result table with product, lot, bin and test time. Replace die with unit in your thinking and change the table and column names.

## Search terms

wafer test yield sql query, bin pareto sql, yield by lot sql, semiconductor yield analysis sql, die level test data query, weekly yield trend sql, worst lots by yield query

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