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
| Column | Type |
|---|---|
| model_id | int |
| model_name | varchar |
| launch_date | date |
Primary key: model_id.
One row per model and colour being launched.
Table: Shipment
| Column | Type |
|---|---|
| shipment_id | int |
| model_id | int |
| arrives_on | date |
| units | int |
Primary key: shipment_id.
Stock arriving at the store for a model.
Table: PreOrder
| Column | Type |
|---|---|
| order_id | int |
| model_id | int |
| ordered_at | datetime |
| units | int |
Primary key: order_id.
One pre-order; units is at least 1. Two orders may share a timestamp.
Examples
Example 1
DeviceModel
| model_id | model_name | launch_date |
|---|---|---|
| 1 | iPhone 16 Pro Desert | 2025-09-19 |
| 2 | iPhone 16 Pro Black | 2025-09-19 |
| 3 | iPhone 16 Teal | 2025-09-19 |
Shipment
| shipment_id | model_id | arrives_on | units |
|---|---|---|---|
| 1 | 1 | 2025-09-15 | 4 |
| 2 | 1 | 2025-09-19 | 2 |
| 3 | 1 | 2025-09-22 | 10 |
| 4 | 2 | 2025-09-17 | 3 |
PreOrder
| order_id | model_id | ordered_at | units |
|---|---|---|---|
| 1 | 1 | 2025-09-12 17:30:00 | 2 |
| 2 | 1 | 2025-09-12 17:30:00 | 3 |
| 3 | 1 | 2025-09-12 17:31:00 | 2 |
| 4 | 1 | 2025-09-12 17:35:00 | 1 |
| 5 | 3 | 2025-09-12 17:40:00 | 1 |
Output
| model_name | filled_orders | waitlisted_orders | units_left |
|---|---|---|---|
| iPhone 16 Pro Desert | 2 | 2 | 1 |
| iPhone 16 Pro Black | 0 | 0 | 3 |
| iPhone 16 Teal | 0 | 1 | 0 |
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.unitsAnother 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 →