Company: Amazon | Difficulty: hard
Amazon wants to identify the product category causing the greatest financial impact from late deliveries at each fulfillment center.
category
First, determine the final shipment state for every completed order. Because an order can have multiple shipment updates, use only the final shipment update for each order. To find the final update, order by updated_at descending. If two updates share the exact same updated_at timestamp, the update with the highest update_id breaks the tie and is considered the final one.
updated_at
update_id
Next, filter for qualifying late deliveries. An order is only considered if its final shipment update has a shipment_status of 'Delivered' and a delivered_date during January 2025. (For example, if an order was delivered but its final status is 'Returned', ignore it entirely). If this final delivery occurred after the order's promised_date, it is considered late.
shipment_status
'Delivered'
delivered_date
'Returned'
promised_date
A late order creates a penalty equal to 10% of its order_value. Ignore orders with a missing order_value.
order_value
Return the category with the highest total late-delivery penalty for each fulfillment center. Include only categories with at least two late orders. If multiple categories tie for the highest penalty at a fulfillment center, return all of them.
Return warehouse_name, category, late_order_count, late_penalty, and category_rank. The late_penalty must be returned with exactly 4 decimal places (e.g. CAST(penalty AS DECIMAL(10,4))). Sort by warehouse_name descending, then category descending. (Note: Since you are only returning the highest penalty category_rank = 1, the specific window function you use for ties beyond rank 1 does not matter).
warehouse_name
late_order_count
late_penalty
category_rank
CAST(penalty AS DECIMAL(10,4))
category_rank = 1
orders
order_id
product_id
warehouse_id
order_status
products
warehouses
shipment_updates
At the 'North Fulfillment Center', 'Electronics' has two late orders (order 1 and 2) and a total penalty of 30.0000, while 'Furniture' has only one late order (order 4, now delivered 2025-01-19 against a promise of 2025-01-18) and is excluded by the two-late-order minimum. At the 'South Fulfillment Center', 'Electronics' has two late orders and the highest penalty of 40.0000; 'Home' also has one late order (order 7) but not two, so it never reaches the ranking step at all.
'North Fulfillment Center'
'Electronics'
1
2
30.0000
'Furniture'
4
2025-01-19
2025-01-18
'South Fulfillment Center'
40.0000
'Home'
7
DROP TABLE IF EXISTS shipment_updates; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS warehouses; CREATE TABLE warehouses ( warehouse_id INT, warehouse_name VARCHAR(80) ); CREATE TABLE products ( product_id INT, category VARCHAR(80) ); CREATE TABLE orders ( order_id INT, product_id INT, warehouse_id INT, promised_date DATE, order_value DECIMAL(10, 2), order_status VARCHAR(20) ); CREATE TABLE shipment_updates ( update_id INT, order_id INT, shipment_status VARCHAR(20), updated_at TIMESTAMP, delivered_date DATE ); INSERT INTO warehouses VALUES (1, 'North Fulfillment Center'), (2, 'South Fulfillment Center'); INSERT INTO products VALUES (101, 'Electronics'), (102, 'Furniture'), (103, 'Electronics'), (104, 'Home'); INSERT INTO orders VALUES (1, 101, 1, DATE '2025-01-10', 100.00, 'Completed'), (2, 101, 1, DATE '2025-01-13', 200.00, 'Completed'), (3, 102, 1, DATE '2025-01-15', 150.00, 'Completed'), (4, 102, 1, DATE '2025-01-18', 100.00, 'Completed'), (5, 103, 2, DATE '2025-01-12', 300.00, 'Completed'), (6, 103, 2, DATE '2025-01-14', 100.00, 'Completed'), (7, 104, 2, DATE '2025-01-16', 250.00, 'Completed'), (8, 104, 2, DATE '2025-01-20', 50.00, 'Completed'); INSERT INTO shipment_updates VALUES (11, 1, 'Shipped', TIMESTAMP '2025-01-08 09:00:00', NULL), (12, 1, 'Delivered', TIMESTAMP '2025-01-12 10:00:00', DATE '2025-01-12'), (21, 2, 'Shipped', TIMESTAMP '2025-01-11 09:00:00', NULL), (22, 2, 'Delivered', TIMESTAMP '2025-01-15 10:00:00', DATE '2025-01-15'), (31, 3, 'Delivered', TIMESTAMP '2025-01-14 10:00:00', DATE '2025-01-14'), (41, 4, 'Delivered', TIMESTAMP '2025-01-19 10:00:00', DATE '2025-01-19'), (51, 5, 'Shipped', TIMESTAMP '2025-01-10 08:00:00', NULL), (52, 5, 'Delivered', TIMESTAMP '2025-01-14 10:00:00', DATE '2025-01-14'), (61, 6, 'Delivered', TIMESTAMP '2025-01-16 10:00:00', DATE '2025-01-16'), (71, 7, 'Delivered', TIMESTAMP '2025-01-17 10:00:00', DATE '2025-01-17'), (81, 8, 'Delivered', TIMESTAMP '2025-01-19 10:00:00', DATE '2025-01-19');
← All SQL Challenges · Amazon Questions · Learn SQL Free