Warehouse Negative Experiences Case

MediumSQL · PostgreSQL

Problem

Take home: analyze a case about why there are negative customer and shopper experiences at a new warehouse.

Using the supplied warehouses, orders, and feedback tables, write a query that summarizes operational performance and negative experience rates for new warehouses. Include warehouses even when they have no completed orders or feedback.

Output

One row per new warehouse.
Columns: warehouse_name, completed_orders, avg_delay_minutes, delayed_order_rate, customer_negative_rate, and shopper_negative_rate.
Rates are percentages, rounded to two decimal places. Treat missing activity as zero where appropriate.
Sort by customer_negative_rate descending, shopper_negative_rate descending, then warehouse_name ascending.

Schema

warehouses

Column	Type	Description
warehouse_idPK	INT	Unique warehouse identifier
warehouse_name	VARCHAR(100)	Warehouse display name
launch_date	DATE	Date the warehouse began operations
is_new	BOOLEAN	Whether the warehouse is classified as new

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
warehouse_id	INT	Warehouse assigned to the order
order_date	DATE	Date the order was placed
promised_minutes	INT	Promised fulfillment duration
actual_minutes	INT	Actual fulfillment duration
status	VARCHAR(20)	Order completion status

feedback

Column	Type	Description
feedback_idPK	INT	Unique feedback identifier
order_id	INT	Order associated with the feedback
audience	VARCHAR(20)	Whether the feedback came from a customer or shopper
sentiment	VARCHAR(20)	Feedback sentiment
issue_type	VARCHAR(40)	Reported experience issue

Tableswarehousesordersfeedback