Detect Anomalies in Delivery Times

Problem Statement

Problem Description A logistics company wants to identify unusually long delivery times across different routes to optimize operations and improve customer satisfaction. Write a SQL query to generate a report highlighting orders that took significantly longer than expected, based on historical route performance. An order is considered an anomaly if its actual delivery time is strictly greater than 1.5 times the average delivery time for that specific route. Schema Table: deliveries Column Name Data Type Constraint Description id INT PRIMARY KEY Unique identifier for the delivery record. order_id INT UNIQUE Identifier for the order being delivered. route_id INT Identifier for the delivery route. delivery_start VARCHAR(19) NOT NULL The date and time when the delivery started. delivery_end VARCHAR(19) NOT NULL The date and time when the delivery ended. Required Output The query should return a result set with the following columns, in this exact order: order_id : Unique identifier for the