SQL Joins for Fulfillment Metrics | Dataford Interview Questions

Write a SQL query using joins to combine orders, shipments, and inventory tables and calculate fulfillment metrics.

Return one row per order. Count all shipment records, but include quantities only from shipments with Shipped or Delivered status. Treat missing shipment quantities as zero and preserve orders without shipments or inventory.

Output

Columns: order_id, sku, ordered_qty, shipment_count, shipped_qty, inventory_qty, fulfillment_percentage, fulfillment_status
Sort by order_id ascending.
Use No units required, Fulfilled, Partially fulfilled, or Unfulfilled for the fulfillment status.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique customer order identifier
sku	VARCHAR(20)	Stock keeping unit ordered
ordered_qty	INT	Quantity requested in the order
order_date	DATE	Date the order was created

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
order_id	INT	Order associated with the shipment
shipped_qty	INT	Quantity assigned to this shipment
status	VARCHAR(20)	Shipment processing status
shipped_date	DATE	Date the shipment left the facility

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory record identifier
sku	VARCHAR(20)	Stock keeping unit held in inventory
warehouse_code	VARCHAR(10)	Warehouse holding the inventory
on_hand_qty	INT	Current available inventory quantity

Your question is SQL Joins for Fulfillment Metrics. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem