Table: Delivery
+-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | date | | customer_pref_delivery_date | date | +-----------------------------+---------+ delivery_id is the column with unique values of this table. Each row contains information about food delivery to a customer that makes an order at some date and specifies a preferred delivery date (on the 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 scheduled.
Write a solution to find the percentage of immediate orders on each unique order_date, rounded to 2 decimal places.
Return the result table ordered by order_date in ascending order.
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 | 2 | 2019-08-01 | 2019-08-01 | | 3 | 1 | 2019-08-01 | 2019-08-01 | | 4 | 3 | 2019-08-02 | 2019-08-13 | | 5 | 3 | 2019-08-02 | 2019-08-02 | | 6 | 2 | 2019-08-02 | 2019-08-02 | | 7 | 4 | 2019-08-03 | 2019-08-03 | | 8 | 1 | 2019-08-03 | 2019-08-03 | | 9 | 5 | 2019-08-04 | 2019-08-08 | | 10 | 2 | 2019-08-04 | 2019-08-18 | +-------------+-------------+------------+-----------------------------+ Output: +------------+----------------------+ | order_date | immediate_percentage | +------------+----------------------+ | 2019-08-01 | 66.67 | | 2019-08-02 | 66.67 | | 2019-08-03 | 100.00 | | 2019-08-04 | 0.00 | +------------+----------------------+ Explanation: - On 2019-08-01 there were three orders, out of those, two were immediate and one was scheduled. So, immediate percentage for that date was 66.67. - On 2019-08-02 there were three orders, out of those, two were immediate and one was scheduled. So, immediate percentage for that date was 66.67. - On 2019-08-03 there were two orders, both were immediate. So, the immediate percentage for that date was 100.00. - On 2019-08-04 there were two orders, both were scheduled. So, the immediate percentage for that date was 0.00. order_date is sorted in ascending order.
Problem Overview: The Delivery table records when a customer places an order and their preferred delivery date. An order is considered immediate when order_date = customer_pref_delivery_date. The task is to compute the percentage of immediate deliveries among relevant orders using SQL aggregation.
Approach 1: Conditional Aggregation with CASE (O(n) query scan)
Scan the Delivery table and classify each order as immediate or scheduled by comparing order_date with customer_pref_delivery_date. Use a CASE WHEN expression to convert this condition into a numeric flag (1 for immediate, 0 otherwise). Then compute the percentage using SUM(flag) / COUNT(*). SQL engines evaluate this with a single pass over the dataset, making the time complexity O(n) where n is the number of rows. Space complexity is O(1) because only aggregate counters are maintained.
If the query requires filtering to the first order per customer or grouping results (for example by month), a common pattern is to use a subquery or CTE to isolate the relevant rows first. A typical method uses MIN(order_date) grouped by customer_id to identify each customer's first order, then joins it back to the Delivery table. After filtering to those rows, apply the same conditional aggregation technique. This keeps the logic readable and ensures the percentage calculation only uses the intended subset of data.
The key insight is that SQL aggregation can convert boolean conditions directly into counts. Instead of iterating row by row in application code, the database engine handles the counting using built‑in aggregate functions like SUM, COUNT, and expressions inside CASE. This pattern appears frequently in database interview questions and analytics queries.
Recommended for interviews: The conditional aggregation approach using CASE and aggregate functions is the expected solution. It demonstrates strong understanding of SQL querying and efficient use of database operations. Brute force processing in application code would require exporting rows and manually counting conditions, which is unnecessary and inefficient compared to a single SQL aggregation query.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Conditional Aggregation with CASE | O(n) | O(1) | Best general SQL solution. Uses a single scan with aggregate functions. |
| CTE + Aggregation | O(n) | O(n) | Useful when filtering to first orders or preparing grouped subsets before computing the percentage. |
Leetcode MEDIUM 2686 - Immediate Food Delivery III - Complete SQL Explained by Everyday Data Science • Everyday Data Science • 474 views views
Practice Immediate Food Delivery III with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor