Apple: Launch-Day Pre-Orders Filled Strictly in Queue Order

Hard
621.

Apple: Launch-Day Pre-Orders Filled Strictly in Queue Order

Before a launch, the supply planning team checks how many pre-orders the launch-day stock will cover. A model's launch-day stock is the sum of units of its shipments arriving on or before its launch_date; later shipments do not count.

Pre-orders are served strictly in queue order — by ordered_at, then order_id — and no order may skip ahead: an order is filled when its units plus the units of every earlier pre-order of the same model fit within the stock; otherwise it is waitlisted. For every model, return model_name, filled_orders, waitlisted_orders and units_left (stock minus the units of filled orders). A model with no pre-orders shows 0, 0 and its full stock. 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: DeviceModel

ColumnType
model_idint
model_namevarchar
launch_datedate

Primary key: model_id.

One row per model and colour being launched.

Table: Shipment

ColumnType
shipment_idint
model_idint
arrives_ondate
unitsint

Primary key: shipment_id.

Stock arriving at the store for a model.

Table: PreOrder

ColumnType
order_idint
model_idint
ordered_atdatetime
unitsint

Primary key: order_id.

One pre-order; units is at least 1. Two orders may share a timestamp.

Example

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

DeviceModel

model_idmodel_namelaunch_date
1iPhone 16 Pro Desert2025-09-19
2iPhone 16 Pro Black2025-09-19
3iPhone 16 Teal2025-09-19

Shipment

shipment_idmodel_idarrives_onunits
112025-09-154
212025-09-192
312025-09-2210
422025-09-173

PreOrder

order_idmodel_idordered_atunits
112025-09-12 17:30:002
212025-09-12 17:30:003
312025-09-12 17:31:002
412025-09-12 17:35:001
532025-09-12 17:40:001

Output

model_namefilled_orderswaitlisted_ordersunits_left
iPhone 16 Pro Desert221
iPhone 16 Pro Black003
iPhone 16 Teal010
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: Launch-Day Pre-Orders Filled Strictly in Queue Order

Before a launch, the supply planning team checks how many pre-orders the launch-day stock will cover.

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #621

Problem statement

Before a launch, the supply planning team checks how many pre-orders the launch-day stock will cover. A model's launch-day stock is the sum of units of its shipments arriving on or before its launch_date; later shipments do not count.

Pre-orders are served strictly in queue order — by ordered_at, then order_id — and no order may skip ahead: an order is filled when its units plus the units of every earlier pre-order of the same model fit within the stock; otherwise it is waitlisted. For every model, return model_name, filled_orders, waitlisted_orders and units_left (stock minus the units of filled orders). A model with no pre-orders shows 0, 0 and its full stock. 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: DeviceModel

ColumnType
model_idint
model_namevarchar
launch_datedate

Primary key: model_id.

One row per model and colour being launched.

Table: Shipment

ColumnType
shipment_idint
model_idint
arrives_ondate
unitsint

Primary key: shipment_id.

Stock arriving at the store for a model.

Table: PreOrder

ColumnType
order_idint
model_idint
ordered_atdatetime
unitsint

Primary key: order_id.

One pre-order; units is at least 1. Two orders may share a timestamp.

Examples

Example 1

DeviceModel

model_idmodel_namelaunch_date
1iPhone 16 Pro Desert2025-09-19
2iPhone 16 Pro Black2025-09-19
3iPhone 16 Teal2025-09-19

Shipment

shipment_idmodel_idarrives_onunits
112025-09-154
212025-09-192
312025-09-2210
422025-09-173

PreOrder

order_idmodel_idordered_atunits
112025-09-12 17:30:002
212025-09-12 17:30:003
312025-09-12 17:31:002
412025-09-12 17:35:001
532025-09-12 17:40:001

Output

model_namefilled_orderswaitlisted_ordersunits_left
iPhone 16 Pro Desert221
iPhone 16 Pro Black003
iPhone 16 Teal010

How to solve Apple: Launch-Day Pre-Orders Filled Strictly in Queue Order

Two quantities meet here: what is on the shelf and what is queued. The shelf is per model: sum the shipments that arrive on or before launch. Put the arrival test in the ON of a LEFT JOIN from DeviceModel, so a model whose stock all arrives late — or that has no shipments — still has a row, with COALESCE turning its NULL sum into 0.

The queue rule "no order may skip ahead" sounds procedural but is a running total: walking the queue in order, the store hands out units until an order no longer fits, and from then on every order waits, because the running total only grows. So an order is filled exactly when SUM(units) over its model's queue up to and including it is at most the stock. order_id breaks ties between orders placed in the same second, which makes the running total deterministic. In the example the Desert model has 6 units on launch day (the shipment of the 22nd is too late): orders 1 and 2 take 5, order 3 would bring the total to 7 and waits, and order 4 waits behind it even though its single unit would have fitted.

Finally LEFT JOIN the queue onto the stock and aggregate per model with conditional sums; a model with no pre-orders gets one NULL queue row, whose CASE yields 0. The alternative recomputes the running total with correlated subqueries — correct, but quadratic in the queue length, while the window is one sorted pass per model.

Reference solution (MySQL)

WITH stock AS (
  SELECT m.model_id, m.model_name, COALESCE(SUM(s.units), 0) AS units
  FROM DeviceModel m
  LEFT JOIN Shipment s ON s.model_id = m.model_id AND s.arrives_on <= m.launch_date
  GROUP BY m.model_id, m.model_name
), queue AS (
  SELECT order_id, model_id, units,
         SUM(units) OVER (PARTITION BY model_id ORDER BY ordered_at, order_id) AS running
  FROM PreOrder
)
SELECT st.model_name,
       SUM(CASE WHEN q.running <= st.units THEN 1 ELSE 0 END) AS filled_orders,
       SUM(CASE WHEN q.running > st.units THEN 1 ELSE 0 END) AS waitlisted_orders,
       st.units - SUM(CASE WHEN q.running <= st.units THEN q.units ELSE 0 END) AS units_left
FROM stock st
LEFT JOIN queue q ON q.model_id = st.model_id
GROUP BY st.model_id, st.model_name, st.units

Another way

SELECT m.model_name,
       (SELECT COUNT(*) FROM PreOrder p WHERE p.model_id = m.model_id AND
          (SELECT SUM(q.units) FROM PreOrder q WHERE q.model_id = p.model_id
             AND (q.ordered_at < p.ordered_at OR (q.ordered_at = p.ordered_at AND q.order_id <= p.order_id)))
          <= (SELECT COALESCE(SUM(s.units), 0) FROM Shipment s WHERE s.model_id = m.model_id AND s.arrives_on <= m.launch_date)) AS filled_orders,
       (SELECT COUNT(*) FROM PreOrder p WHERE p.model_id = m.model_id AND
          (SELECT SUM(q.units) FROM PreOrder q WHERE q.model_id = p.model_id
             AND (q.ordered_at < p.ordered_at OR (q.ordered_at = p.ordered_at AND q.order_id <= p.order_id)))
          > (SELECT COALESCE(SUM(s.units), 0) FROM Shipment s WHERE s.model_id = m.model_id AND s.arrives_on <= m.launch_date)) AS waitlisted_orders,
       (SELECT COALESCE(SUM(s.units), 0) FROM Shipment s WHERE s.model_id = m.model_id AND s.arrives_on <= m.launch_date)
       - COALESCE((SELECT SUM(p.units) FROM PreOrder p WHERE p.model_id = m.model_id AND
          (SELECT SUM(q.units) FROM PreOrder q WHERE q.model_id = p.model_id
             AND (q.ordered_at < p.ordered_at OR (q.ordered_at = p.ordered_at AND q.order_id <= p.order_id)))
          <= (SELECT COALESCE(SUM(s.units), 0) FROM Shipment s WHERE s.model_id = m.model_id AND s.arrives_on <= m.launch_date)), 0) AS units_left
FROM DeviceModel m

← Apple: Apps Breaking Into Their Category's Top Three · Apple: Apple Pay Rolling Seven-Day Fraud Rate →