1173. Immediate Food Delivery I
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 82.7%
Problem Breakdown
The problem “Immediate Food Delivery I” involves analyzing food delivery data to determine the percentage of orders that were delivered immediately (i.e., within 30 minutes). The input consists of two tables: Delivery and Customer. The Delivery table contains columns for order ID, customer ID, restaurant ID, order date, and actual delivery time. The Customer table contains columns for customer ID and name. The output should be a single value representing the percentage of orders delivered immediately.
Inputs:
- Two tables:
DeliveryandCustomer.Delivery: Contains columnsorder_id,customer_id,restaurant_id,order_date, andactual_delivery_time.Customer: Contains columnscustomer_idandname.
Outputs:
- A single value representing the percentage of orders delivered immediately.
Implicit Constraints or Tricky Edge Cases:
- The
Deliverytable may be empty. - There might be duplicate entries in the
Deliverytable. - The
actual_delivery_timecould be null or invalid. - The percentage should be rounded to two decimal places.
Intuition
To solve this problem, we need to count the number of orders that were delivered immediately and divide it by the total number of orders. A brute-force approach would involve iterating through each row in the Delivery table, checking if the delivery time is within 30 minutes, and then calculating the percentage. However, this approach would be inefficient with a high time complexity.
The key insight is to use SQL aggregation functions to count the number of immediate deliveries and the total number of orders efficiently. By leveraging SQL’s built-in capabilities, we can achieve a more optimal solution.
Approach
- Filter Immediate Deliveries: Use a
CASEstatement within an aggregate function to count the number of orders where the delivery time is within 30 minutes. - Count Total Orders: Use another aggregate function to count the total number of orders.
- Calculate Percentage: Divide the count of immediate deliveries by the total number of orders and multiply by 100 to get the percentage.
- Round the Result: Use the
ROUNDfunction to round the result to two decimal places.
Complexity Analysis
- Time Complexity: O(n), where n is the number of rows in the
Deliverytable. This is because we are scanning through each row once. - Space Complexity: O(1), as we are using a constant amount of extra space for storing intermediate results.
Solution
SELECT
ROUND(
SUM(CASE WHEN actual_delivery_time <= 30 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
2
) AS immediate_percentage
FROM
Delivery;
Edge Cases Handled
- Empty
DeliveryTable: The query will returnNULL, which can be handled by adding a check for an empty table before executing the query. - Duplicate Entries: The query counts each row independently, so duplicates are naturally handled.
- Null or Invalid
actual_delivery_time: TheCASEstatement ensures that only valid delivery times within 30 minutes are counted. - Rounding: The
ROUNDfunction ensures the result is rounded to two decimal places.