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.
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.
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:
GROUP BY with the MIN() function.SUM() within a CASE WHEN statement.SQL
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.
This implementation relies on the Pandas library in Python to filter, group and compute the solution.
In this solution, we use Pandas to:
GROUP BY functionality to find the first order for each customer using groupby and idxmin().order_date equals customer_pref_delivery_date.Python
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.
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.
SQL
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.
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.
Time Complexity: O(N log N) due to sorting of deliveries.
Space Complexity: O(K), where K is the number of unique customers.
We can use a subquery to first find the first order of each user, and then calculate the proportion of instant orders.
MySQL
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.
MySQL
| 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. |
| Python Pandas Approach | Time Complexity: O(N), as we need to process each order to determine which are the first for their customers. |
| SQL Query with Window Functions | Time Complexity: O(N), where N is the number of rows in the Delivery table. |
| Iterative Approach with Data Structures | Time Complexity: O(N log N) due to sorting of deliveries. |
| Subquery | — |
| Window Function | — |
| 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 |
Immediate Food Delivery II | Leetcode 1174 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 16,817 views views
Watch 9 more video solutions →Practice Immediate Food Delivery II with our built-in code editor and test cases.
Practice on FleetCode