Weekly Order Accuracy SQL

MediumSQL · PostgreSQL

Problem

Write a SQL query to analyze weekly order accuracy and missing-item rates by fulfillment center using orders, order_items, and complaints tables.

Use all available order dates. Treat an order as accurate when it has no complaint. Treat a missing-item rate as missing units divided by ordered units. Label orders without a fulfillment center as Unknown.

Output

One row per calendar week and fulfillment center
Columns: week_start, fulfillment_center, total_orders, accurate_orders, accuracy_rate_pct, total_units, missing_units, and missing_item_rate_pct
Include centers with no complaints and avoid counting an order or item more than once
Sort by week_start, then fulfillment_center

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
order_date	DATE	Date the order was placed
fulfillment_center	VARCHAR(50)	Fulfillment center handling the order
order_status	VARCHAR(30)	Current order status

order_items

Column	Type	Description
item_idPK	INT	Unique order-item identifier
order_id	INT	Order containing the item
item_name	VARCHAR(100)	CookUnity meal or item name
quantity	INT	Units ordered for the item

complaints

Column	Type	Description
complaint_idPK	INT	Unique complaint identifier
order_id	INT	Order associated with the complaint
item_id	INT	Specific item associated with the complaint, when available
complaint_type	VARCHAR(40)	Complaint category
missing_quantity	INT	Units reported missing for a missing-item complaint

Tablesordersorder_itemscomplaints