Incoming Lot Rejection Rate by Supplier — SQL Problem
Goods-receipt inspection checks every incoming lot of bought-out parts.
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #263
Problem statement
Goods-receipt inspection checks every incoming lot of bought-out parts. A lot code looks like LOT-SUNDRAM-2024-117: the second hyphen-separated part is the supplier code, and supplier codes have different lengths.
For every supplier, return supplier, lots (the number of lots inspected), received (total units received), rejected (total units rejected) and rejection_pct — rejected units as a percentage of received units, rounded to two decimals. Order by rejection_pct from highest to lowest, then by supplier.
Tables
Table: GoodsReceiptInspection
| Column | Type |
|---|---|
| inspection_id | int |
| lot_code | varchar |
| qty_received | int |
| qty_rejected | int |
Primary key: inspection_id.
Lot codes are LOT-<SUPPLIER>-<YEAR>-<SEQ> in upper case; qty_received is always positive.
Examples
Example 1
GoodsReceiptInspection
| inspection_id | lot_code | qty_received | qty_rejected |
|---|---|---|---|
| 1 | LOT-SUNDRAM-2024-117 | 2000 | 14 |
| 2 | LOT-RANE-2024-031 | 1500 | 45 |
| 3 | LOT-SUNDRAM-2024-118 | 1000 | 6 |
| 4 | LOT-MINDA-2024-009 | 800 | 0 |
| 5 | LOT-RANE-2025-002 | 500 | 5 |
| 6 | LOT-BOSCH-2025-014 | 1200 | 18 |
Output
| supplier | lots | received | rejected | rejection_pct |
|---|---|---|---|---|
| RANE | 2 | 2000 | 50 | 2.5 |
| BOSCH | 1 | 1200 | 18 | 1.5 |
| SUNDRAM | 2 | 3000 | 20 | 0.67 |
| MINDA | 1 | 800 | 0 | 0 |
How to solve Incoming Lot Rejection Rate by Supplier
The grouping key is inside a delimited string. SUBSTRING_INDEX(lot_code, '-', 2) returns LOT-SUNDRAM (everything before the second hyphen) and SUBSTRING_INDEX(…, '-', -1) takes the last piece of that, SUNDRAM. Nesting the two is the standard way to pull the n-th field out of a delimited code in MySQL, and it does not care how long the field is. The alternative finds the second hyphen with LOCATE('-', lot_code, 5) — searching from just after LOT- — and cuts between the two.
Grouping by that expression gives one row per supplier. The rejection rate is pooled — total rejected over total received — so a 3,000-unit lot weighs more than a 300-unit one, which is what a supplier scorecard wants; averaging per-lot rates would not. qty_received is always positive, so the division is safe, and the result is rounded to two decimals as asked.
The order is worst supplier first, alphabetical among ties. One scan with a string function per row.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(lot_code, '-', 2), '-', -1) AS supplier,
COUNT(*) AS lots,
SUM(qty_received) AS received,
SUM(qty_rejected) AS rejected,
ROUND(100 * SUM(qty_rejected) / SUM(qty_received), 2) AS rejection_pct
FROM GoodsReceiptInspection
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(lot_code, '-', 2), '-', -1)
ORDER BY rejection_pct DESC, supplierAnother way
SELECT supplier, COUNT(*) AS lots, SUM(qty_received) AS received, SUM(qty_rejected) AS rejected,
ROUND(SUM(qty_rejected) * 100 / SUM(qty_received), 2) AS rejection_pct
FROM (SELECT SUBSTRING(lot_code, 5, LOCATE('-', lot_code, 5) - 5) AS supplier, qty_received, qty_rejected
FROM GoodsReceiptInspection) t
GROUP BY supplier ORDER BY rejection_pct DESC, supplier← Solar Generation Sharp Day-Over-Day Drops · EV Charging Peak and Off-Peak Revenue →