Apple: Component Batches Below Their Pooled Test Yield

Medium
611.

Apple: Component Batches Below Their Pooled Test Yield

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

ColumnTypeValues
batch_idint
suppliervarchar
componentenumOLED panel, A-series chip, Camera module, Battery cell
units_testedint
units_passedint

Primary key: batch_id.

One row per batch received; units_tested is always positive and units_passed never exceeds it.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

ComponentBatch

batch_idsuppliercomponentunits_testedunits_passed
1Supplier AOLED panel10096
2Supplier BOLED panel200180
3Supplier AOLED panel10093
4Supplier CCamera module5049
5Supplier DCamera module150147
6Supplier BBattery cell8070

Output

batch_idcomponentsupplier
2OLED panelSupplier B
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
batch_idint
suppliervarchar
componentenum(OLED panel, A-series chip, Camera module, Battery cell)
units_testedint
units_passedint

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_idsuppliercomponentunits_testedunits_passed
1Supplier AOLED panel10096
2Supplier BOLED panel200180
3Supplier AOLED panel10093
4Supplier CCamera module5049
5Supplier DCamera module150147
6Supplier BBattery cell8070

Output

batch_idcomponentsupplier
2OLED panelSupplier 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_tested

Another 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 →