Immediate Food Delivery I - Solution & Explanation
Problem Statement
Table: Delivery
+-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | date | | customer_pref_delivery_date | date | +-----------------------------+---------+ delivery_id is the primary key (column with unique values) of this table. The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).
If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.
Write a solution to find the percentage of immediate orders in the table, rounded to 2 decimal places.
The result format is in the following example.
Example 1:
Input: Delivery table: +-------------+-------------+------------+-----------------------------+ | delivery_id | customer_id | order_date | customer_pref_delivery_date | +-------------+-------------+------------+-----------------------------+ | 1 | 1 | 2019-08-01 | 2019-08-02 | | 2 | 5 | 2019-08-02 | 2019-08-02 | | 3 | 1 | 2019-08-11 | 2019-08-11 | | 4 | 3 | 2019-08-24 | 2019-08-26 | | 5 | 4 | 2019-08-21 | 2019-08-22 | | 6 | 2 | 2019-08-11 | 2019-08-13 | +-------------+-------------+------------+-----------------------------+ Output: +----------------------+ | immediate_percentage | +----------------------+ | 33.33 | +----------------------+ Explanation: The orders with delivery id 2 and 3 are immediate while the others are scheduled.
Approach Overview
Problem Overview: The Delivery table stores food orders with the actual order_date and the customer’s customer_pref_delivery_date. An order is considered immediate if both dates are the same. The task is to compute the percentage of orders that were delivered immediately across all records.
Approach 1: Conditional SUM Aggregation (O(n) time, O(1) space)
This solution scans the Delivery table once and counts how many orders satisfy the immediate delivery condition. In MySQL, boolean expressions evaluate to 1 (true) or 0 (false). That allows you to directly compute the number of immediate orders using SUM(order_date = customer_pref_delivery_date). The total number of orders is obtained with COUNT(*). Dividing the two and multiplying by 100 yields the percentage.
The key insight is that MySQL treats the equality comparison as a numeric value during aggregation. Instead of writing a longer CASE WHEN expression, the condition itself becomes the value being summed. This keeps the query short and efficient while still performing a full table aggregation. The final result is rounded to two decimal places using ROUND().
This approach works well for problems involving conditional counting or ratio calculations. SQL aggregation functions such as SUM, COUNT, and AVG are commonly used in database interview questions to compute metrics directly from raw records. Understanding how boolean expressions interact with aggregation is particularly useful in SQL queries.
An equivalent implementation can use SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END). Both versions run in linear time because the database engine performs a single pass over the table while maintaining aggregate counters.
Recommended for interviews: The conditional aggregation approach is the expected solution. Interviewers want to see that you can compute ratios directly using SQL aggregates instead of writing multiple subqueries. The concise SUM(condition) pattern demonstrates strong familiarity with SQL aggregation and keeps the query both readable and efficient.
Solution
We can use the sum function to count the number of instant orders, and then divide it by the total number of orders. Since the problem requires a percentage, we need to multiply by 100. Finally, we can use the round function to keep two decimal places.
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SUM with Boolean Condition | O(n) | O(1) | Best in MySQL where boolean expressions evaluate to 1 or 0 during aggregation |
| SUM with CASE WHEN | O(n) | O(1) | Portable SQL solution that works across most database systems |
Video Solution
LeetCode 1173 DoorDash Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 3,895 views views
Watch 5 more video solutions →Frequently Asked Questions
Is Immediate Food Delivery I easy or hard?
Immediate Food Delivery I Python/Java solution
How to solve Immediate Food Delivery I in O(n)?
What is the best approach for Immediate Food Delivery I?
Is Immediate Food Delivery I asked at Google/Amazon/Meta?
What data structure is used in Immediate Food Delivery I?
What is the time complexity of Immediate Food Delivery I?
Ready to solve this problem?
Practice Immediate Food Delivery I with our built-in code editor and test cases.
Practice on FleetCode