Amazon-style Company ChallengeMediumVerified answerSQLite live

Warehouse Performance Analysis

For every warehouse, how many employees are assigned, how many shipments have been dispatched, and what is the storage capacity?

  • Joins
  • Aggregation
  • Sorting
  • Distinct values

Challenge brief

Understand the request

Operations Leadership is evaluating warehouse efficiency and needs a snapshot of staffing, shipment volume, and storage capacity across all facilities.

Show employee count, shipment count, and capacity for every warehouse including empty ones.

Return

  • warehouse_name
  • location
  • employee_count
  • shipment_count
  • capacity

Constraints

  • Include every warehouse, including facilities with no employees or shipments
  • Employee and shipment counts must remain at their own entity grains
  • Return warehouses from highest to lowest shipment count

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

warehouses

  • warehouse_idINTEGER
  • warehouse_nameVARCHAR(100)
  • locationVARCHAR(100)
  • capacityINTEGER

employees

  • employee_idINTEGER
  • warehouse_idINTEGER

shipments

  • shipment_idINTEGER
  • warehouse_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The warehouses table is the anchor — start FROM warehouses. Employees and shipments are both one-to-many relationships to warehouses, so you need two separate LEFT JOINs. Use LEFT JOIN (not INNER) to keep DFW1 which has no employees or shipments.

Hint 2

LEFT JOIN employees on warehouse_id, LEFT JOIN shipments on warehouse_id. Because both joins fan out independently, you MUST use COUNT(DISTINCT e.employee_id) and COUNT(DISTINCT s.shipment_id) — otherwise rows multiply and counts inflate. GROUP BY all non-aggregated columns.

Hint 3

Scaffold: preserve warehouses while joining employees and shipments; count each child entity distinctly, group by warehouse attributes, and sort by shipment count.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT w.warehouse_name, w.location, COUNT(DISTINCT e.employee_id) AS employee_count, COUNT(DISTINCT s.shipment_id) AS shipment_count, w.capacity FROM warehouses w LEFT JOIN employees e ON w.warehouse_id = e.warehouse_id LEFT JOIN shipments s ON w.warehouse_id = s.warehouse_id GROUP BY w.warehouse_id, w.warehouse_name, w.location, w.capacity ORDER BY shipment_count DESC;

Why this works

Two independent one-to-many JOINs on the same table create a cartesian fan-out: if SEA1 has 3 employees and 4 shipments, a naive COUNT(*) returns 12 rows. COUNT(DISTINCT employee_id) and COUNT(DISTINCT shipment_id) collapse that back to the true counts of 3 and 4.

Success check

3 warehouses — SEA1 (4 shipments), LAX1 (2 shipments), DFW1 (0 shipments)

Expected result

Use this output to verify values, aliases, ordering, and row count.

warehouse_namelocationemployee_countshipment_countcapacity
SEA1Seattle, WA34100000
LAX1Los Angeles, CA22150000
DFW1Dallas, TX00120000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.