Immediate Food Delivery II

Immediate Food Delivery II

What is this problem about?

The Immediate Food Delivery II interview question is a more advanced version of the previous problem. Instead of looking at all orders, you only care about the first order placed by each customer. You need to find the percentage of these first orders that were "immediate" (order date matches preferred delivery date).

Why is this asked in interviews?

Companies like Apple and Amazon use this "Medium" SQL problem to test your knowledge of Subqueries and Grouping. It requires you to first identify a subset of the data (the first order for every user) and then perform a calculation on that subset. This is a common pattern in "Cohort Analysis" and "User Retention" metrics where only the initial interaction is analyzed.

Algorithmic pattern used

The problem follows the Filtering by Minimum Value pattern.

1. Find First Orders: Group by customer_id and find the MIN(order_date) for each.
2. Filter Table: Join the original table with this subquery (or use a tuple IN clause) to select only the rows corresponding to those first orders.
3. Aggregate: Among these rows, calculate the percentage of immediate orders using the same logic as Version I.

Example explanation

• Customer 1 orders: Aug 1 (Immediate), Aug 5 (Scheduled). First order is Aug 1.
• Customer 2 orders: Aug 3 (Scheduled), Aug 4 (Immediate). First order is Aug 3.

1. Total first orders: 2.
2. Immediate first orders: 1 (Customer 1).
3. Result: $50.00\%$.

Common mistakes candidates make

• Missing "First Order" logic: Calculating the percentage across all orders instead of just the first one for each customer.
• Duplicate Dates: If a customer has multiple orders on their first day, you should specify how to handle ties (though usually, the problem schema prevents this or any order on that day is considered).
• Inefficient Joins: Using complex self-joins when a simple (customer_id, order_date) IN (...) query is much cleaner.

Interview preparation tip

Master the "First Row per Group" pattern. You can solve it using GROUP BY with MIN(), or using window functions like RANK() OVER(PARTITION BY customer_id ORDER BY order_date). In an interview, mention both to show breadth of knowledge.