Immediate Food Delivery II - Solution & Explanation

MediumDatabase9 min readAsked at: Amazon, Microsoft, Meta +5

Problem Statement

Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the column of unique values of this table.
The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).

If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.

The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.

Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

The result format is in the following example.

Example 1:

Input: 
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
Output: 
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
Explanation: 
The customer id 1 has a first order with delivery id 1 and it is scheduled.
The customer id 2 has a first order with delivery id 2 and it is immediate.
The customer id 3 has a first order with delivery id 5 and it is scheduled.
The customer id 4 has a first order with delivery id 7 and it is immediate.
Hence, half the customers have immediate first orders.

Approach Overview

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.

Approach 1: SQL Approach with Subquery

This approach uses SQL to isolate the first order for each customer and then calculate the percentage of immediate orders.

The query consists of two main parts:

An inner query which finds the first order date for each customer via a GROUP BY with the MIN() function.
An outer query that calculates the percentage of immediate orders from these first orders using a conditional SUM() within a CASE WHEN statement.

Code

SQL

Complexity

Time Complexity: O(N), as we need to scan all deliveries to determine the first order per customer, where N is the number of records.
Space Complexity: O(K), where K is the number of unique customers since the auxiliary data structure to store these results depends on the number of unique customers.

Try this approach in the editor →

Approach 2: Python Pandas Approach

This implementation relies on the Pandas library in Python to filter, group and compute the solution.

In this solution, we use Pandas to:

Simulate the SQL GROUP BY functionality to find the first order for each customer using groupby and idxmin().
Calculate the percentage of immediate first orders by comparing the subset of first orders where order_date equals customer_pref_delivery_date.

Code

Python

Complexity

Time Complexity: O(N), as we need to process each order to determine which are the first for their customers.
Space Complexity: O(K), with K being the number of unique customers, since the additional memory used scales with the number of unique customer IDs.

Try this approach in the editor →

Approach 3: SQL Query with Window Functions

This approach uses SQL window functions to determine the first order for each customer by order date. We utilize the ROW_NUMBER() function to rank each customer's orders and filter out the first one. Afterwards, we count which of these are immediate by comparing the order date with the customer's preferred delivery date.

Firstly, we use the ROW_NUMBER() function partitioned by customer_id and ordered by order_date to assign a sequence number to each order within a customer group. The innermost query captures this for each customer's orders. We then filter these to only include those with rn = 1, effectively giving us the first order for each customer. Finally, we calculate the percentage of immediate orders by taking the ratio of immediate first orders to total first orders.

Code

SQL

Complexity

Time Complexity: O(N), where N is the number of rows in the Delivery table.
Space Complexity: O(N) due to the storage of intermediate results in the temporary table.

Try this approach in the editor →

Approach 4: Iterative Approach with Data Structures

This approach involves using a programming language to iterate through the data stored in a collection, like a list or an array. We first sort the deliveries by customer ID and order date, then track the first order for each customer using a dictionary or hash map. Finally, we check each first order for immediacy and calculate the percentage of immediate deliveries.

We first use a dictionary to store the first order date and preferred delivery date of each customer. By iterating over the sorted list of deliveries, we ensure only the earliest order for each customer is stored. We then use this data to count how many of these first orders are immediate and calculate their percentage.

Code

Python C++

Python

C++

Complexity

Time Complexity: O(N log N) due to sorting of deliveries.
Space Complexity: O(K), where K is the number of unique customers.

Try this approach in the editor →

Approach 5: Subquery

We can use a subquery to first find the first order of each user, and then calculate the proportion of instant orders.

Code

MySQL

Try this approach in the editor →

Approach 6: Window Function

We can use the RANK() window function to rank the orders of each user in ascending order by order date, and then filter out the orders with a rank of 1, which are the first orders of each user. After that, we can calculate the proportion of instant orders.

Code

MySQL

Try this approach in the editor →

Complexity Comparison

Approach	Complexity
SQL Approach with Subquery	Time Complexity: O(N), as we need to scan all deliveries to determine the first order per customer, where N is the number of records. Space Complexity: O(K), where K is the number of unique customers since the auxiliary data structure to store these results depends on the number of unique customers.
Python Pandas Approach	Time Complexity: O(N), as we need to process each order to determine which are the first for their customers. Space Complexity: O(K), with K being the number of unique customers, since the additional memory used scales with the number of unique customer IDs.
SQL Query with Window Functions	Time Complexity: O(N), where N is the number of rows in the Delivery table. Space Complexity: O(N) due to the storage of intermediate results in the temporary table.
Iterative Approach with Data Structures	Time Complexity: O(N log N) due to sorting of deliveries. Space Complexity: O(K), where K is the number of unique customers.
Subquery	—
Window Function	—

Detailed Complexity Analysis

Approach	Time	Space	When to Use
SQL with Subquery	O(n)	O(n)	When practicing classic SQL aggregation and joins
SQL with Window Functions	O(n)	O(n)	Best approach in modern SQL engines supporting window functions
Python Pandas	O(n)	O(n)	Data analysis workflows or Python-based data processing
Iterative Hash Map	O(n)	O(n)	Implementing database-style grouping in general programming languages

Video Solution

Immediate Food Delivery II | Leetcode 1174 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 18,803 views views

Watch 9 more video solutions →

Frequently Asked Questions

Immediate Food Delivery II Python solution

In Python, the problem can be solved using Pandas by sorting the DataFrame by customer_id and order_date, then using groupby('customer_id').first() to get the earliest order. Compare order_date with customer_pref_delivery_date and compute the percentage of immediate deliveries.

Is Immediate Food Delivery II easy or hard?

Immediate Food Delivery II is generally considered a medium difficulty SQL problem. The challenge comes from identifying the first order per customer and then calculating a percentage from that filtered dataset. Familiarity with GROUP BY or window functions makes the problem straightforward.

How to solve Immediate Food Delivery II in O(n)?

First identify the earliest order for each customer using either MIN(order_date) with GROUP BY or a window function like ROW_NUMBER(). Filter to keep only those first orders. Count how many have order_date equal to customer_pref_delivery_date and divide by the total number of first orders to compute the percentage.

What is the best approach for Immediate Food Delivery II?

The SQL window function approach using ROW_NUMBER() is typically the best solution. Partition rows by customer_id and order by order_date to identify the first order per customer. Then calculate the percentage of those rows where order_date equals customer_pref_delivery_date. The query runs in O(n) time and is concise in modern SQL engines.

Is Immediate Food Delivery II asked at Google/Amazon/Meta?

Database aggregation and window-function problems like Immediate Food Delivery II appear frequently in SQL interview rounds at companies such as Amazon, Meta, and data-focused roles at Google. These questions test your ability to group records, find first events, and compute ratios from filtered data.

What data structure is used in Immediate Food Delivery II?

In SQL, the solution relies on grouping operations or window functions rather than explicit data structures. In a programming-language implementation, a hash map keyed by customer_id can store the earliest order for each customer, enabling an O(n) solution.

What is the time complexity of Immediate Food Delivery II?

Most optimal solutions run in O(n) time where n is the number of rows in the Delivery table. SQL engines scan the table once and perform grouping or window partitioning by customer_id. Space complexity is generally O(n) for intermediate grouping or ranking results.

Ready to solve this problem?

Practice Immediate Food Delivery II with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance53.1%

Approaches6

Reading time9 min

Asked at

Amazon Microsoft Meta Starbucks DoorDash

Practice this problem

Open in Editor

Problem Statement

Approach Overview

Approach 1: SQL Approach with Subquery

Code

Complexity

Approach 2: Python Pandas Approach

Code

Complexity

Approach 3: SQL Query with Window Functions

Code

Complexity

Approach 4: Iterative Approach with Data Structures

Code

Complexity

Approach 5: Subquery

Code

Approach 6: Window Function

Code

Complexity Comparison

Detailed Complexity Analysis

Video Solution

Frequently Asked Questions

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents