Solution Explanation for LeetCode 1174: Immediate Food Delivery II

This problem asks us to calculate the percentage of "immediate" orders among the first orders placed by each customer. An "immediate" order is defined as one where the order_date and customer_pref_delivery_date are the same. The "first order" for a customer is the order with the earliest order_date.

The solutions presented use SQL queries to achieve this. Let's break down both approaches:

Solution 1: Using Subqueries

This solution uses a subquery to identify the first order for each customer and then calculates the percentage of immediate orders within those first orders.

MySQL Code:

SELECT
    ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage
FROM Delivery
WHERE
    (customer_id, order_date) IN (
        SELECT customer_id, MIN(order_date)
        FROM Delivery
        GROUP BY 1
    );

Explanation:

1. Inner Subquery: SELECT customer_id, MIN(order_date) FROM Delivery GROUP BY 1 This part finds the minimum order_date (the first order) for each customer_id. GROUP BY 1 groups the results by the first column (customer_id).

2. Outer Query: The outer query then filters the Delivery table, keeping only the rows where the (customer_id, order_date) combination matches one of the first orders identified in the subquery.

3. Percentage Calculation: AVG(order_date = customer_pref_delivery_date) calculates the average of a boolean expression. order_date = customer_pref_delivery_date evaluates to 1 (true) if it's an immediate order and 0 (false) otherwise. The average then represents the proportion of immediate orders. ROUND(..., 2) rounds the result to two decimal places.

Time Complexity: The time complexity is dominated by the subquery, which requires a full table scan (or an index lookup if an index exists on customer_id and order_date). Therefore, the time complexity is O(N log N) or O(N) depending on whether an appropriate index exists. N is the number of rows in the Delivery table.

Solution 2: Using Window Functions

This solution leverages the power of window functions to improve efficiency.

MySQL Code:

WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY customer_id
                ORDER BY order_date
            ) AS rk
        FROM Delivery
    )
SELECT
    ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage
FROM T
WHERE rk = 1;

Explanation:

1. Common Table Expression (CTE): The CTE T uses the RANK() window function. PARTITION BY customer_id partitions the data by customer, and ORDER BY order_date ranks orders within each customer's partition based on their order_date. The earliest order gets rank 1, the next earliest gets rank 2, and so on (in case of ties, all tied orders get the same rank).

2. Main Query: The main query filters T to only include rows where rk = 1 (the first orders). It then calculates the average of order_date = customer_pref_delivery_date similar to Solution 1, to find the percentage of immediate orders among these first orders.

Time Complexity: The RANK() window function generally has a time complexity of O(N log N) or better with proper indexing, so this solution also has a time complexity that is O(N log N) or O(N) depending on the index.

In Summary: Both solutions provide correct results. Solution 2 using window functions is generally considered more elegant and potentially more efficient, especially for larger datasets, as window functions are often optimized better than subqueries. However, the actual performance difference might depend on the database system's optimizer and the presence of appropriate indexes.