Immediate Food Delivery II - Video Solutions

Problem Overview: The Delivery table stores food delivery orders with the order date and the customer’s preferred delivery date. A delivery is considered immediate when order_date = customer_pref_delivery_date. The task is to compute the percentage of customers whose first order was delivered immediately.

Approach 1: SQL Approach with Subquery (Time: O(n), Space: O(n))

The key step is identifying each customer's first order. Use a subquery that groups rows by customer_id and extracts MIN(order_date). Join this result back to the Delivery table to retrieve the actual first order rows. After that, compute how many of those rows satisfy order_date = customer_pref_delivery_date. Divide the count of immediate deliveries by the total number of first orders and multiply by 100. This approach works well in most SQL engines and clearly separates the steps: find first orders, then compute the ratio. Useful when practicing fundamental SQL grouping and filtering patterns.

Approach 2: SQL Query with Window Functions (Time: O(n), Space: O(n))

Window functions provide a cleaner way to identify the first order per customer. Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) to assign a rank to each order within a customer group. Filter rows where row_number = 1 to keep only the first order. From those rows, calculate the percentage where order_date = customer_pref_delivery_date. This avoids an explicit join and keeps the logic inside one query. Preferred when working with modern SQL systems that support window functions.

Approach 3: Python Pandas Approach (Time: O(n), Space: O(n))

Load the table into a Pandas DataFrame and sort by customer_id and order_date. Use groupby('customer_id').first() to extract each customer's earliest order. Then compare order_date and customer_pref_delivery_date to determine which rows are immediate. Compute the percentage by dividing the number of immediate rows by the total number of first orders. This approach mirrors the SQL logic but uses DataFrame operations from Pandas.

Approach 4: Iterative Approach with Data Structures (Time: O(n), Space: O(n))

In a general programming language, store the earliest order for each customer using a hash map keyed by customer_id. Iterate through all rows and update the stored record if a smaller order_date appears. After collecting the earliest order per customer, iterate over the stored records and count how many are immediate deliveries. Compute the percentage from these counts. This mirrors the SQL grouping behavior using basic data structures.

Recommended for interviews: The SQL window function approach is usually the cleanest and easiest to reason about. It shows that you understand partitioning and ranking within groups. The subquery solution demonstrates strong fundamentals and works even when window functions are unavailable, but the window-based query is typically the expected optimal solution.