Final project · SQLite

Handoff a replenishment and exception queue

Project dataset

My saved work →

Project dataset Fresh dataset challenge →Each dataset keeps its own attempts.

Your task

Return sku, available, due_units, position, action, order_qty, sorted by sku. Keep every stock SKU once.
Eligible inbound is confirmed with eta <= 2026-10-07, including overdue arrivals and arrivals exactly on the cutoff. Collapse identical PO copies; exclude all variants if a PO key has conflicting fields. Ignore unmatched inbound SKUs.
available = on_hand - reserved. due_units is the eligible inbound sum, defaulting to zero. position = available + due_units; NULL on_hand leaves available and position NULL.
Action priority: NULL on_hand gives recount and NULL order_qty; negative available gives resolve_reservations and NULL order_qty; otherwise position < reorder_point gives reorder; equality or above gives hold.
For reorder, round target - position up to a whole number of pack_size units. For hold return numeric 0. Do not turn unknown stock into zero.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

skustring
availableNULL or number
due_unitsnumber
positionNULL or number
actionstring
order_qtyNULL or number

Expected output: 54 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Preserve SQL NULL unless the task explicitly gives a replacement rule. Check JOINs for extra copies of rows.

Reconciliation checks

Output must have 54 rows, one per stock SKU.
Eligible matched inbound units: 136; recount items: 5; reservation conflicts: 3.
Every reorder quantity must be a positive pack multiple and reach target; no recount or reservation conflict may receive a numeric order quantity.

Need a next step?

Hint 1: where to start

Establish the business-key and date policies before joining or aggregating.

Hint 2: build the method

Write down the eligible keys before aggregating. Inspect each anomaly against the output contract.

Hint 3: check your work

Use the reconciliation counts after constructing the complete output; a matching total alone does not prove the rows are correct.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

88 original source records.

Data dictionary

stock.sku text: Unique product key, including discontinued items needing review.
stock.on_hand integer | null: Physical units. NULL requires a recount and must stay unknown.
stock.reserved integer: Committed units; more than known stock is a count conflict.
stock.reorder_point integer: Reorder only when projected position is strictly below this number.
stock.target integer: Target units after replenishment.
stock.pack_size integer: Positive supplier pack size; round an order up to complete packs.
inbound.po_id text: PO key; collapse exact duplicates and reject all conflicting variants.
inbound.sku text: May be unmatched; orphan inbound never creates a stock SKU.
inbound.units integer: Positive units on the order.
inbound.eta text: ISO arrival date; confirmed arrivals on or before cutoff count.
inbound.status text: Only confirmed arrivals contribute.

View database setup and records

CREATE TABLE stock (sku TEXT, on_hand INTEGER, reserved INTEGER, reorder_point INTEGER, target INTEGER, pack_size INTEGER);
INSERT INTO stock VALUES
('SKU-100',NULL,0,10,30,4),
('SKU-101',11,1,10,35,6),
('SKU-102',0,0,10,40,10),
('SKU-103',1,4,10,30,4),
('SKU-104',4,4,10,35,6),
('SKU-105',5,5,10,40,10),
('SKU-106',6,6,10,30,4),
('SKU-107',7,7,10,35,6),
('SKU-108',8,0,10,40,10),
('SKU-109',9,1,10,30,4),
('SKU-110',10,2,10,35,6),
('SKU-111',11,3,10,40,10),
('SKU-112',12,4,10,30,4),
('SKU-113',NULL,5,10,35,6),
('SKU-114',14,6,10,40,10),
('SKU-115',15,7,10,30,4),
('SKU-116',16,0,10,35,6),
('SKU-117',17,1,10,40,10),
('SKU-118',18,2,10,30,4),
('SKU-119',19,3,10,35,6),
('SKU-120',20,4,10,40,10),
('SKU-121',21,5,10,30,4),
('SKU-122',22,6,10,35,6),
('SKU-123',23,7,10,40,10),
('SKU-124',24,0,10,30,4),
('SKU-125',25,1,10,35,6),
('SKU-126',NULL,2,10,40,10),
('SKU-127',27,3,10,30,4),
('SKU-128',28,4,10,35,6),
('SKU-129',29,5,10,40,10),
('SKU-130',0,6,10,30,4),
('SKU-131',1,7,10,35,6),
('SKU-132',2,0,10,40,10),
('SKU-133',3,1,10,30,4),
('SKU-134',4,2,10,35,6),
('SKU-135',5,3,10,40,10),
('SKU-136',6,4,10,30,4),
('SKU-137',7,5,10,35,6),
('SKU-138',8,6,10,40,10),
('SKU-139',NULL,7,10,30,4),
('SKU-140',10,0,10,35,6),
('SKU-141',11,1,10,40,10),
('SKU-142',12,2,10,30,4),
('SKU-143',13,3,10,35,6),
('SKU-144',14,4,10,40,10),
('SKU-145',15,5,10,30,4),
('SKU-146',16,6,10,35,6),
('SKU-147',17,7,10,40,10),
('SKU-148',18,0,10,30,4),
('SKU-149',19,1,10,35,6),
('SKU-150',20,2,10,40,10),
('SKU-151',21,3,10,30,4),
('SKU-152',NULL,4,10,35,6),
('SKU-153',23,5,10,40,10);
CREATE TABLE inbound (po_id TEXT, sku TEXT, units INTEGER, eta TEXT, status TEXT);
INSERT INTO inbound VALUES
('PO-100','SKU-100',4,'2026-10-01','cancelled'),
('PO-101','SKU-101',5,'2026-10-02','confirmed'),
('PO-102','SKU-102',6,'2026-10-03','confirmed'),
('PO-103','SKU-103',7,'2026-10-04','confirmed'),
('PO-104','SKU-104',8,'2026-10-05','confirmed'),
('PO-105','SKU-105',9,'2026-10-06','confirmed'),
('PO-106','SKU-106',10,'2026-10-07','confirmed'),
('PO-107','SKU-107',4,'2026-10-08','cancelled'),
('PO-108','SKU-108',5,'2026-10-09','confirmed'),
('PO-109','SKU-109',6,'2026-10-10','confirmed'),
('PO-110','SKU-110',7,'2026-10-01','confirmed'),
('PO-111','SKU-111',8,'2026-10-02','confirmed'),
('PO-112','SKU-112',9,'2026-10-03','confirmed'),
('PO-113','SKU-113',10,'2026-10-04','confirmed'),
('PO-114','SKU-114',4,'2026-10-05','cancelled'),
('PO-115','SKU-115',5,'2026-10-06','confirmed'),
('PO-116','SKU-116',6,'2026-10-07','confirmed'),
('PO-117','SKU-117',7,'2026-10-08','confirmed'),
('PO-118','SKU-118',8,'2026-10-09','confirmed'),
('PO-119','SKU-119',9,'2026-10-10','confirmed'),
('PO-120','SKU-120',10,'2026-10-01','confirmed'),
('PO-121','SKU-121',4,'2026-10-02','cancelled'),
('PO-122','SKU-122',5,'2026-10-03','confirmed'),
('PO-123','SKU-123',6,'2026-10-04','confirmed'),
('PO-124','SKU-124',7,'2026-10-05','confirmed'),
('PO-125','SKU-125',8,'2026-10-06','confirmed'),
('PO-126','SKU-126',9,'2026-10-07','confirmed'),
('PO-127','SKU-127',10,'2026-10-08','confirmed'),
('PO-128','SKU-128',4,'2026-10-09','cancelled'),
('PO-129','SKU-129',5,'2026-10-10','confirmed'),
('PO-101','SKU-101',5,'2026-10-02','confirmed'),
('PO-102','SKU-102',999,'2026-10-03','confirmed'),
('EDGE-100','SKU-104',7,'2026-10-07','confirmed'),
('ORPH-100','UNKNOWN',40,'2026-10-07','confirmed');

Your work

Active 0:00 · Paused 0s

SQL query

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))), due AS (SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku), positions AS (SELECT s.*,s.on_hand-s.reserved AS available,COALESCE(d.due_units,0) AS due_units,s.on_hand-s.reserved+COALESCE(d.due_units,0) AS position FROM stock s LEFT JOIN due d ON s.sku=d.sku) SELECT sku,available,due_units,position,CASE WHEN on_hand IS NULL THEN 'recount' WHEN available<0 THEN 'resolve_reservations' WHEN position<reorder_point THEN 'reorder' ELSE 'hold' END AS action,CASE WHEN on_hand IS NULL OR available<0 THEN NULL WHEN position<reorder_point THEN ((target-position+pack_size-1)/pack_size)*pack_size ELSE 0 END AS order_qty FROM positions ORDER BY sku;

Turn stock snapshots and inbound orders into a checked replenishment queue.