Amazon wants to identify the product category causing the greatest financial impact from late deliveries at each fulfillment center. For every completed order delivered during January 2025, determine whether the package arrived after its promised delivery date. A late order creates a penalty equal to 10% of its 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. Because an order can have multiple shipment updates, use only the final shipment update for each order. If two updates have the same timestamp, the update with the greater `update_id` is considered final. Return `warehouse_name`, `category`, `late_order_count`, `late_penalty`, and `category_rank`. Sort by `warehouse_name`, then `category`. ### Table Schema | Table | Column | Type | |---|---|---| | orders | order_id | integer | | orders | product_id | integer | | orders | warehouse_id | integer | | orders | promised_date | date | | orders | order_value | decimal(10,2) | | orders | order_status | varchar(20) | | products | product_id | integer | | products | category | varchar(80) | | warehouses | warehouse_id | integer | | warehouses | warehouse_name | varchar(80) | | shipment_updates | update_id | integer | | shipment_updates | order_id | integer | | shipment_updates | shipment_status | varchar(20) | | shipment_updates | updated_at | timestamp | | shipment_updates | delivered_date | date | ### Example Input: orders | order_id | product_id | warehouse_id | promised_date | order_value | order_status | |---:|---:|---:|---|---:|---| | 1 | 101 | 1 | 2025-01-10 | 100.00 | Completed | | 2 | 101 | 1 | 2025-01-13 | 200.00 | Completed | | 3 | 102 | 1 | 2025-01-15 | 150.00 | Completed | | 4 | 102 | 1 | 2025-01-18 | 100.00 | Completed | | 5 | 103 | 2 | 2025-01-12 | 300.00 | Completed | | 6 | 103 | 2 | 2025-01-14 | 100.00 | Completed | | 7 | 104 | 2 | 2025-01-16 | 250.00 | Completed | | 8 | 104 | 2 | 2025-01-20 | 50.00 | Completed | ### Example Output Explanation At the North Fulfillment Center, Electronics has two late orders and a total penalty of 30.00, while Furniture has only one late order and is excluded. At the South Fulfillment Center, Electronics has the highest penalty of 40.00, so it is returned instead of Tablets.
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-17 10:00:00', DATE '2025-01-17'), (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');