Immediate Food Delivery II | LeetCode 1174

This chapter breaks down the solution for LeetCode 1174: Immediate Food Delivery II, a classic SQL interview problem that combines aggregate functions (MIN), conditional evaluation (IF), subqueries, and percentage calculation with rounding.

1. Why Do We Need This? (Problem Statement & Status Quo)

In food delivery platforms and e-commerce applications, customer retention heavily depends on meeting or exceeding delivery expectations. Business stakeholders frequently analyze delivery performance metrics to evaluate service reliability.

Specifically, management wants to know the percentage of immediate orders among all customers' first orders.

• An Immediate Order occurs when the customer's preferred delivery date is exactly equal to the order date.
• A Scheduled Order occurs when the preferred delivery date is later than the order date.
• A First Order is defined as the earliest order placed by a specific customer based on the order date.

2. Core Mental Model & Table Schema

The problem provides a single table named Delivery with four core columns:

Real-Life Analogy

Mental Hook: Imagine you run a restaurant chain. You want to measure first impressions. You don't care about a regular customer's 5th or 10th order today; you want to measure how often a brand new customer gets their food immediately on their very first visit versus having their delivery scheduled for later.

3. Step-by-Step Data Walkthrough (Example Breakdown)

Consider the following sample input Delivery table:

Step A: Filter for Each Customer's First Order

We first isolate each customer's earliest order (MIN(order_date)):

• Customer 1: Ordered on 2019-08-01 (delivery pref 2019-08-02) vs 2019-08-11. Earliest = 2019-08-01.
• Customer 2: Ordered on 2019-08-02 vs 2019-08-11. Earliest = 2019-08-02.
• Customer 3: Ordered on 2019-08-24 vs 2019-08-21. Earliest = 2019-08-21.
• Customer 4: Ordered on 2019-08-09. Earliest = 2019-08-09.

Step B: Classify Immediate vs. Scheduled on First Orders

Now we evaluate whether order_date == customer_pref_delivery_date for those specific first orders:

1. Customer 1 (2019-08-01 vs 2019-08-02) $\rightarrow$ Scheduled (S)
2. Customer 2 (2019-08-02 vs 2019-08-02) $\rightarrow$ Immediate (I)
3. Customer 3 (2019-08-21 vs 2019-08-21) $\rightarrow$ Immediate (I)
4. Customer 4 (2019-08-09 vs 2019-08-09) $\rightarrow$ Immediate (I)

Wait, let's verify customer 3's dates carefully from the table: Row 5 has order_date = 2019-08-21 and Row 4 has 2019-08-24. The minimum date for Customer 3 is 2019-08-21, and its preferred date is 2019-08-21 (Immediate). Let's recalculate proportions: out of 4 first orders, 3 are immediate (Customer 2, 3, 4) and 1 is scheduled (Customer 1).

4. Naive Approach vs. Subquery Architecture

The Naive Trap

If we simply evaluate all rows in the table without filtering for first orders, our counts will be polluted by repeat customers who order multiple times, violating the core business requirement ("percentage of immediate orders in the first orders of all customers").

The Subquery Solution Architecture

To solve this cleanly, we split our logic into two layers:

1. Inner Subquery: Extracts each customer_id and their corresponding minimum order_date.
2. Outer Query: Evaluates delivery immediacy on that restricted dataset, computes the percentage, and rounds to two decimal places.

Outer Query (Computes percentage, ROUND, SUM)
   ^
   | filters rows matching
   |
Subquery / Derived Table (Groups by customer_id, finds MIN(order_date))
   ^
   | queries raw data from
   |
Delivery Table

5. Constructing the Inner Subquery (Finding First Orders)

Let's write the SQL snippet to extract every customer along with their absolute earliest order date:

SELECT 
    customer_id, 
    MIN(order_date) AS first_order_date
FROM 
    Delivery
GROUP BY 
    customer_id;

When executed against our dataset, this guarantees we get precisely one row per unique customer containing their earliest order date.

6. Building the Complete SQL Query

We tie our subquery into a WHERE clause tuple comparison to filter the main table, then use an IF statement inside a SUM aggregator to calculate the percentage.

SELECT 
    ROUND(
        SUM(IF(order_date = customer_pref_delivery_date, 1, 0)) * 100.0 / COUNT(DISTINCT customer_id), 
        2
    ) AS immediate_percentage
FROM 
    Delivery
WHERE 
    (customer_id, order_date) IN (
        SELECT 
            customer_id, 
            MIN(order_date)
        FROM 
            Delivery
        GROUP BY 
            customer_id
    );

7. Deep-Dive Code Breakdown & Mechanics

Let's dissect each functional block in our production query:

1. IF(order_date = customer_pref_delivery_date, 1, 0):
   • Evaluates row by row whether the order date matches the customer preference.
   • If true, returns 1 (Immediate); if false, returns 0 (Scheduled).
2. SUM(...) * 100.0 / COUNT(DISTINCT customer_id):
   • SUM(...) aggregates all the 1s, giving us the total count of immediate first orders.
   • Multiplying by 100.0 prevents integer division truncation in SQL engines (ensuring floating-point math).
   • Dividing by COUNT(DISTINCT customer_id) computes the final ratio against the total unique customer pool.
3. ROUND(..., 2):
   • Rounds the resulting floating-point percentage to exactly two decimal places as requested by the problem statement.
4. WHERE (customer_id, order_date) IN (...):
   • Leverages a multi-column tuple check (Row Constructor) to ensure we only evaluate rows where both the customer_id and order_date match a customer's verified first order.

8. Summary and Key Takeaways

• Understand Business Constraints: Always identify whether an aggregation requires all records or specifically first-time occurrences (e.g., MIN(order_date)).
• Subqueries as Filters: Using tuple comparisons like WHERE (col1, col2) IN (SELECT ...) allows precise multi-column filtering against aggregated datasets.
• Conditional Summation: Combining SUM() with IF() (or CASE WHEN) is an efficient idiom in MySQL/SQL for counting boolean conditions without resorting to complex joins.
• Preventing Integer Division: Always multiply numerator counts by 100.0 (instead of integer 100) to maintain decimal precision in SQL division.
• Data Hygiene with DISTINCT: Ensure aggregation counts align with unique entity identifiers (customer_id) rather than raw row counts.
• Rounding Output: Use ROUND(val, 2) to format financial or statistical percentages neatly for presentation layers.