LeetCode 2686 - Immediate Food Delivery III

LeetCode Problem 2686

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

This problem asks us to calculate the percentage of immediate food delivery orders for each unique order_date.

The Delivery table contains one row per order. Each row includes:

• delivery_id: A unique identifier for the delivery.
• customer_id: The customer who placed the order.
• order_date: The date when the order was placed.
• customer_pref_delivery_date: The date the customer prefers to receive the order.

An order is considered immediate if:

order_date == customer_pref_delivery_date

Otherwise, the order is considered scheduled.

Our task is to group all orders by order_date and compute the percentage of immediate orders for that day.

The percentage formula is:

$$\text{immediate percentage} = \frac{\text{number of immediate orders}}{\text{total orders}} \times 100$$

The result must satisfy two requirements:

1. The percentage must be rounded to 2 decimal places.
2. The rows must be sorted by order_date in ascending order.

The key detail is that we calculate percentages per order date, not globally across the entire dataset. Multiple customers may place orders on the same day, and the same customer can appear multiple times.

Since this is a database problem, performance matters, but the operation we need is naturally aggregation based. SQL provides grouping and aggregate functions that make this efficient.

An important observation is that the problem guarantees every row has valid dates and a unique delivery_id. There are no concerns about duplicate records or invalid ordering. We also do not need to handle division-by-zero because every order_date group must contain at least one order.

Some edge cases could trip up a naive implementation:

• A date where all orders are immediate, which should produce 100.00.
• A date where no orders are immediate, which should produce 0.00.
• A date with only one order, where the percentage becomes either 100.00 or 0.00.
• Multiple orders from the same customer on the same day, since the problem counts orders, not unique customers.

Approaches

Brute Force Approach

A brute-force solution would process each unique order_date separately.

For every distinct date, we could scan the entire table again to count:

1. The total number of orders on that date.
2. The number of immediate orders on that date.

After collecting these counts, we would compute the percentage and append it to the result.

This approach works because every date independently checks all rows and correctly counts matching records. However, it is inefficient because for every unique date we repeatedly scan the entire dataset.

If there are n rows and d distinct dates, the total complexity becomes approximately O(d × n). In the worst case, every row could have a unique date, making this O(n²).

Optimal Approach

The key observation is that this problem is fundamentally a grouping and aggregation problem.

Instead of repeatedly scanning the table, we can process all rows once, grouping by order_date.

For each date, we need only two values:

• Total number of orders.
• Number of immediate orders.

SQL aggregation functions make this very efficient.

We can:

1. Group rows by order_date.
2. Count all rows for the denominator.
3. Use a conditional count or average for immediate deliveries.
4. Multiply by 100 and round to two decimal places.

A particularly elegant insight is that in SQL, a boolean condition evaluates to 1 when true and 0 when false in many databases. This means:

AVG(order_date = customer_pref_delivery_date)

directly computes the fraction of immediate orders.

Multiplying by 100 converts it to a percentage.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n²)	O(1)	Repeatedly scans the table for each date
Optimal	O(n log n)	O(1)	Single grouping pass with sorting for ordered output

The sorting factor comes from ordering results by order_date. The aggregation itself is linear.

Algorithm Walkthrough

1. Group all rows by order_date.

We need one output row per unique date, so grouping ensures all orders from the same day are processed together. 2. For each grouped date, count total orders.

This becomes the denominator for the percentage calculation. 3. Identify immediate orders.

An order is immediate when:

order_date = customer_pref_delivery_date

We count how many rows satisfy this condition. 4. Compute the percentage.

Use:

$$\frac{\text{immediate orders}}{\text{total orders}} \times 100$$ 5. Round to two decimal places.

The problem explicitly requires two decimal precision. 6. Sort by order_date in ascending order.

This ensures the output matches the required format.

Why it works

The algorithm works because every order belongs to exactly one order_date group. Within each group, we accurately count both the total number of orders and the subset that are immediate. Since the percentage formula is mathematically correct for each independent group, and every order is processed exactly once, the final output is guaranteed to be correct.

Python Solution

Since this is a database problem, the LeetCode submission is written in SQL.

# This problem is solved using SQL, not Python.

SELECT
    order_date,
    ROUND(
        AVG(order_date = customer_pref_delivery_date) * 100,
        2
    ) AS immediate_percentage
FROM Delivery
GROUP BY order_date
ORDER BY order_date;

The solution groups records by order_date, which ensures that all orders placed on the same day are processed together.

The expression:

order_date = customer_pref_delivery_date

evaluates to 1 for immediate orders and 0 for scheduled ones in MySQL. Taking the AVG() of these values gives the fraction of immediate orders for that date.

Multiplying by 100 converts the fraction into a percentage, and ROUND(..., 2) guarantees exactly two decimal places. Finally, ORDER BY order_date ensures ascending chronological output.

Go Solution

This problem is solved in SQL, so there is no Go implementation required for LeetCode submission.

// SQL problem, no Go implementation required.

Unlike algorithmic coding problems, database problems on LeetCode require an SQL query as the final answer. Therefore, language-specific concerns such as slices, maps, integer overflow, or nil handling do not apply here.

Worked Examples

Example 1

Input:

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

We group by order_date.

Date: 2019-08-01

Order	Immediate?
1	No
2	Yes
3	Yes

State:

total_orders = 3
immediate_orders = 2
percentage = (2 / 3) * 100 = 66.67

Date: 2019-08-02

Order	Immediate?
4	No
5	Yes
6	Yes

State:

total_orders = 3
immediate_orders = 2
percentage = 66.67

Date: 2019-08-03

Order	Immediate?
7	Yes
8	Yes

State:

total_orders = 2
immediate_orders = 2
percentage = 100.00

Date: 2019-08-04

Order	Immediate?
9	No
10	No

State:

total_orders = 2
immediate_orders = 0
percentage = 0.00

Final result:

order_date	immediate_percentage
2019-08-01	66.67
2019-08-02	66.67
2019-08-03	100.00
2019-08-04	0.00

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n log n)	Aggregation is linear, sorting grouped dates contributes the logarithmic factor
Space	O(1)	SQL aggregation uses constant extra space beyond internal database operations

The query scans the table once to aggregate values by order_date. Grouping itself is linear relative to the number of rows. Since the final result must be ordered chronologically, sorting the grouped dates contributes an additional logarithmic factor.

Test Cases

# These represent expected SQL outcomes conceptually.

# Example case
assert {
    "2019-08-01": 66.67,
    "2019-08-02": 66.67,
    "2019-08-03": 100.00,
    "2019-08-04": 0.00
} == {
    "2019-08-01": 66.67,
    "2019-08-02": 66.67,
    "2019-08-03": 100.00,
    "2019-08-04": 0.00
}  # provided example

# Single immediate order
assert {"2024-01-01": 100.00} == {
    "2024-01-01": 100.00
}  # one order, immediate

# Single scheduled order
assert {"2024-01-01": 0.00} == {
    "2024-01-01": 0.00
}  # one order, scheduled

# Mixed orders
assert {"2024-01-01": 50.00} == {
    "2024-01-01": 50.00
}  # half immediate

# All immediate
assert {"2024-01-01": 100.00} == {
    "2024-01-01": 100.00
}  # every order immediate

# All scheduled
assert {"2024-01-01": 0.00} == {
    "2024-01-01": 0.00
}  # every order scheduled

# Multiple dates
assert {
    "2024-01-01": 50.00,
    "2024-01-02": 100.00
} == {
    "2024-01-01": 50.00,
    "2024-01-02": 100.00
}  # grouping across dates

Test	Why
Provided example	Verifies correctness against the official example
Single immediate order	Ensures 100.00 is computed correctly
Single scheduled order	Ensures 0.00 is handled correctly
Mixed orders	Verifies percentage calculation
All immediate	Tests maximum percentage
All scheduled	Tests minimum percentage
Multiple dates	Validates grouping behavior

Edge Cases

A date with all immediate orders

A common bug is incorrectly averaging or counting immediate deliveries, which could prevent the result from reaching exactly 100.00. For example, if every order on a date has matching delivery dates, the average boolean value becomes 1.0, producing 100.00. The implementation handles this naturally through AVG(...) * 100.

A date with no immediate orders

Another edge case occurs when all deliveries are scheduled. Some implementations accidentally divide incorrectly or return NULL. Since the boolean comparison evaluates to 0 for every row, the average becomes 0, and the final percentage correctly evaluates to 0.00.

Multiple orders from the same customer

A naive implementation might mistakenly group by both customer_id and order_date, which would produce incorrect percentages. The problem explicitly asks for percentages per order_date, meaning every order counts independently. The query correctly groups only by order_date.

Dates with only one order

When a date contains a single order, the denominator becomes 1. This can expose rounding mistakes or integer division bugs in some implementations. Using AVG() avoids these issues entirely because SQL automatically handles decimal computation, yielding either 100.00 or 0.00 as appropriate.