Apple: Component Batches Below Their Pooled Test Yield
Supplier quality engineers flag incoming component batches that test worse than the norm for that component.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #611
Problem statement
Supplier quality engineers flag incoming component batches that test worse than the norm for that component. A batch's yield is units_passed / units_tested; a component's pooled yield is the total units passed over the total units tested across all batches of that component, from every supplier.
Return batch_id, component and supplier for every batch whose yield is strictly lower than its component's pooled yield. Return the rows in any order.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: ComponentBatch
| Column | Type |
|---|---|
| batch_id | int |
| supplier | varchar |
| component | enum(OLED panel, A-series chip, Camera module, Battery cell) |
| units_tested | int |
| units_passed | int |
Primary key: batch_id.
One row per batch received; units_tested is always positive and units_passed never exceeds it.
Examples
Example 1
ComponentBatch
| batch_id | supplier | component | units_tested | units_passed |
|---|---|---|---|---|
| 1 | Supplier A | OLED panel | 100 | 96 |
| 2 | Supplier B | OLED panel | 200 | 180 |
| 3 | Supplier A | OLED panel | 100 | 93 |
| 4 | Supplier C | Camera module | 50 | 49 |
| 5 | Supplier D | Camera module | 150 | 147 |
| 6 | Supplier B | Battery cell | 80 | 70 |
Output
| batch_id | component | supplier |
|---|---|---|
| 2 | OLED panel | Supplier B |
How to solve Apple: Component Batches Below Their Pooled Test Yield
The benchmark is the pooled yield, SUM(units_passed) / SUM(units_tested) per component — not the average of the batch yields, which would weight a 50-unit batch the same as a 200-unit one. Compute those two sums per component in a derived table, join each batch to its component's row, and compare.
The comparison passed_b / tested_b < passed_c / tested_c can be cross-multiplied because every units_tested is positive: passed_b × tested_c < passed_c × tested_b. That keeps it in exact integers, so a batch exactly at the pooled yield — for example the only batch of its component, which always equals its own pool — is correctly not below it.
A correlated subquery that computes the pooled ratio per batch gives the same rows, and a window SUM(…) OVER (PARTITION BY component) attaches the component totals to each batch without a join. All three read the table twice (or once with the window).
Reference solution (MySQL)
SELECT b.batch_id, b.component, b.supplier
FROM ComponentBatch b
JOIN (
SELECT component, SUM(units_passed) AS passed, SUM(units_tested) AS tested
FROM ComponentBatch
GROUP BY component
) c ON c.component = b.component
WHERE b.units_passed * c.tested < c.passed * b.units_testedAnother way
SELECT b.batch_id, b.component, b.supplier FROM ComponentBatch b WHERE b.units_passed / b.units_tested < (SELECT SUM(x.units_passed) / SUM(x.units_tested) FROM ComponentBatch x WHERE x.component = b.component)Another way
SELECT batch_id, component, supplier FROM (SELECT batch_id, component, supplier, units_passed * SUM(units_tested) OVER (PARTITION BY component) AS lhs, units_tested * SUM(units_passed) OVER (PARTITION BY component) AS rhs FROM ComponentBatch) t WHERE lhs < rhs← Apple: Apple Pay Monthly Volume Change by Country · Apple: Each Product Line's Share of Store Revenue →