Restock the Dark Stores

A grocery delivery company runs small warehouses ("dark stores"). Every morning the overnight deliveries in delivery_lines must be applied to shelf_stock.

Write the statement(s) that update shelf_stock so that, for every (store_id, sku) that appears in delivery_lines:

• if the row already exists, units_on_hand grows by the total units received for that store and SKU, and last_restock becomes the latest received_on among those lines;
• if it does not exist, a new row is inserted with that total and that latest date.

A store and SKU can appear on several delivery lines. Rows with no deliveries stay unchanged, and nothing is deleted. Do not return anything: the judge compares the whole shelf_stock table afterwards, sorted by store_id, sku.

Tables

shelf_stock — Current stock, one row per store and SKU.

delivery_lines — Overnight deliveries waiting to be applied; one row per delivery line.

Example

shelf_stock

delivery_lines

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Changing Data · problem 63 of 70 · SQLite

The idea

An upsert is an insert that turns into an update when the key already exists. The primary key (store_id, sku) is the conflict target.

Aggregate first. Several delivery lines can share a store and SKU, so GROUP BY store_id, sku turns them into a single row with SUM(units_received) and MAX(received_on). Feed that into INSERT ... ON CONFLICT (store_id, sku) DO UPDATE:

• a new pair is inserted as is;
• an existing pair runs the update, where units_on_hand is the current value and excluded.units_on_hand is the delivered total, so adding them gives the new level.

The WHERE true is a SQLite habit: when an upsert reads from a SELECT whose FROM clause runs straight into ON CONFLICT, the parser can take ON for a join condition and fail. A WHERE clause removes the ambiguity.

Without upsert support you do the same in two steps (alternative solution): an UPDATE ... FROM the aggregated deliveries, then an INSERT ... SELECT of the pairs that do not exist yet. The update has to run first, otherwise the rows you just inserted would get their deliveries added twice.

Solution

INSERT INTO shelf_stock (store_id, sku, units_on_hand, last_restock)
SELECT store_id, sku, SUM(units_received), MAX(received_on)
FROM delivery_lines
WHERE true
GROUP BY store_id, sku
ON CONFLICT (store_id, sku) DO UPDATE SET
  units_on_hand = units_on_hand + excluded.units_on_hand,
  last_restock  = excluded.last_restock;

Another way

-- 1) top up rows that already exist
UPDATE shelf_stock
SET units_on_hand = units_on_hand + d.total_units,
    last_restock  = d.latest
FROM (
  SELECT store_id, sku, SUM(units_received) AS total_units, MAX(received_on) AS latest
  FROM delivery_lines
  GROUP BY store_id, sku
) AS d
WHERE shelf_stock.store_id = d.store_id
  AND shelf_stock.sku = d.sku;

-- 2) add the pairs the table has never seen
INSERT INTO shelf_stock (store_id, sku, units_on_hand, last_restock)
SELECT store_id, sku, SUM(units_received), MAX(received_on)
FROM delivery_lines AS dl
WHERE NOT EXISTS (
  SELECT 1 FROM shelf_stock AS s
  WHERE s.store_id = dl.store_id AND s.sku = dl.sku
)
GROUP BY store_id, sku;

Why common answers fail

Overwrites the stock level with the delivered quantity instead of adding to what is already on the shelf.

INSERT INTO shelf_stock (store_id, sku, units_on_hand, last_restock)
SELECT store_id, sku, SUM(units_received), MAX(received_on)
FROM delivery_lines WHERE true
GROUP BY store_id, sku
ON CONFLICT (store_id, sku) DO UPDATE SET
  units_on_hand = excluded.units_on_hand,
  last_restock = excluded.last_restock;

INSERT OR REPLACE deletes the existing row and inserts a new one, so the units already on the shelf are lost.

INSERT OR REPLACE INTO shelf_stock (store_id, sku, units_on_hand, last_restock)
SELECT store_id, sku, SUM(units_received), MAX(received_on)
FROM delivery_lines
GROUP BY store_id, sku;

Applies the lines one by one without aggregating, so last_restock ends up as whichever line was processed last rather than the latest date.

INSERT INTO shelf_stock (store_id, sku, units_on_hand, last_restock)
SELECT store_id, sku, units_received, received_on
FROM delivery_lines WHERE true
ON CONFLICT (store_id, sku) DO UPDATE SET
  units_on_hand = units_on_hand + excluded.units_on_hand,
  last_restock = excluded.last_restock;

Only updates existing rows and forgets to insert SKUs that a store is stocking for the first time.

UPDATE shelf_stock
SET units_on_hand = units_on_hand + (
      SELECT SUM(units_received) FROM delivery_lines d
      WHERE d.store_id = shelf_stock.store_id AND d.sku = shelf_stock.sku),
    last_restock = (
      SELECT MAX(received_on) FROM delivery_lines d
      WHERE d.store_id = shelf_stock.store_id AND d.sku = shelf_stock.sku)
WHERE EXISTS (
  SELECT 1 FROM delivery_lines d
  WHERE d.store_id = shelf_stock.store_id AND d.sku = shelf_stock.sku);

What the interviewer is checking

Checks that you know an atomic upsert and its excluded pseudo-table, and that you aggregate the source before merging instead of trusting it to have one row per key.

Mistakes to avoid

• Overwriting units_on_hand with the delivered amount instead of adding to it.
• Using INSERT OR REPLACE, which deletes the old row and loses its stock.
• Not aggregating the source, so the date (or any non-additive column) depends on processing order.
• In the two-step version, inserting before updating, which double-counts new rows.

Follow-ups to expect

• Keep last_restock from going backwards if a late line has an older date (use MAX(last_restock, excluded.last_restock)).
• Skip delivery lines with zero units (filter them in the SELECT).
• Clear delivery_lines once applied, safely (wrap both statements in a transaction).

In real work

Inventory sync jobs, daily metric rollups and "merge staging into target" ETL steps are all upserts keyed on a natural key.

Practice next