Immediate Food Delivery III - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read

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 with unique values of this table.
Each row contains information about food delivery to a customer that makes an order at some date and specifies a preferred delivery date (on the 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 scheduled.

Write a solution to find the percentage of immediate orders on each unique order_date, rounded to 2 decimal places.

Return the result table ordered by order_date in ascending order.

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-01 | 2019-08-01                  |
| 3           | 1           | 2019-08-01 | 2019-08-01                  |
| 4           | 3           | 2019-08-02 | 2019-08-13                  |
| 5           | 3           | 2019-08-02 | 2019-08-02                  |
| 6           | 2           | 2019-08-02 | 2019-08-02                  |
| 7           | 4           | 2019-08-03 | 2019-08-03                  |
| 8           | 1           | 2019-08-03 | 2019-08-03                  |
| 9           | 5           | 2019-08-04 | 2019-08-08                  |
| 10          | 2           | 2019-08-04 | 2019-08-18                  |
+-------------+-------------+------------+-----------------------------+
Output: 
+------------+----------------------+
| order_date | immediate_percentage |
+------------+----------------------+
| 2019-08-01 | 66.67                |
| 2019-08-02 | 66.67                |
| 2019-08-03 | 100.00               |
| 2019-08-04 | 0.00                 |
+------------+----------------------+
Explanation: 
- On 2019-08-01 there were three orders, out of those, two were immediate and one was scheduled. So, immediate percentage for that date was 66.67.
- On 2019-08-02 there were three orders, out of those, two were immediate and one was scheduled. So, immediate percentage for that date was 66.67.
- On 2019-08-03 there were two orders, both were immediate. So, the immediate percentage for that date was 100.00.
- On 2019-08-04 there were two orders, both were scheduled. So, the immediate percentage for that date was 0.00.
order_date is sorted in ascending order.

Approach Overview

Problem Overview: The Delivery table records when a customer places an order and their preferred delivery date. An order is considered immediate when order_date = customer_pref_delivery_date. The task is to compute the percentage of immediate deliveries among relevant orders using SQL aggregation.

Approach 1: Conditional Aggregation with CASE (O(n) query scan)

Scan the Delivery table and classify each order as immediate or scheduled by comparing order_date with customer_pref_delivery_date. Use a CASE WHEN expression to convert this condition into a numeric flag (1 for immediate, 0 otherwise). Then compute the percentage using SUM(flag) / COUNT(*). SQL engines evaluate this with a single pass over the dataset, making the time complexity O(n) where n is the number of rows. Space complexity is O(1) because only aggregate counters are maintained.

If the query requires filtering to the first order per customer or grouping results (for example by month), a common pattern is to use a subquery or CTE to isolate the relevant rows first. A typical method uses MIN(order_date) grouped by customer_id to identify each customer's first order, then joins it back to the Delivery table. After filtering to those rows, apply the same conditional aggregation technique. This keeps the logic readable and ensures the percentage calculation only uses the intended subset of data.

The key insight is that SQL aggregation can convert boolean conditions directly into counts. Instead of iterating row by row in application code, the database engine handles the counting using built‑in aggregate functions like SUM, COUNT, and expressions inside CASE. This pattern appears frequently in database interview questions and analytics queries.

Recommended for interviews: The conditional aggregation approach using CASE and aggregate functions is the expected solution. It demonstrates strong understanding of SQL querying and efficient use of database operations. Brute force processing in application code would require exporting rows and manually counting conditions, which is unnecessary and inefficient compared to a single SQL aggregation query.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Conditional Aggregation with CASE	O(n)	O(1)	Best general SQL solution. Uses a single scan with aggregate functions.
CTE + Aggregation	O(n)	O(n)	Useful when filtering to first orders or preparing grouped subsets before computing the percentage.

Video Solution

Leetcode MEDIUM 2686 - Immediate Food Delivery III - Complete SQL Explained by Everyday Data Science • Everyday Data Science • 492 views views

Frequently Asked Questions

Is Immediate Food Delivery III easy or hard?

Immediate Food Delivery III is rated Medium difficulty. The SQL syntax itself is straightforward, but recognizing that conditional aggregation can compute percentages efficiently is the key insight.

Immediate Food Delivery III Python/Java solution

This problem is designed as a SQL query challenge rather than an algorithmic coding task. The expected solution is written in MySQL using CASE expressions and aggregate functions instead of Python or Java.

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

Use conditional aggregation. Write a CASE expression that returns 1 when order_date equals customer_pref_delivery_date and 0 otherwise. Summing this value gives the number of immediate orders, and dividing by the total count yields the percentage in a single O(n) query.

What is the best approach for Immediate Food Delivery III?

The best approach uses SQL conditional aggregation. Compare order_date with customer_pref_delivery_date to classify orders as immediate, then compute the percentage using SUM(CASE WHEN ...) divided by COUNT(*). This solution scans the table once and runs in O(n) time.

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

SQL aggregation and analytics-style queries like Immediate Food Delivery III appear frequently in interviews at companies such as Amazon, Meta, and data-focused roles at Google. The problem tests familiarity with SQL filtering, grouping, and conditional counting.

What data structure is used in Immediate Food Delivery III?

The problem relies on relational database tables and SQL aggregation rather than traditional data structures. Core operations include filtering rows, grouping data, and computing aggregates like COUNT and SUM with CASE conditions.

What is the time complexity of Immediate Food Delivery III?

The SQL query typically runs in O(n) time because the database performs a single scan of the Delivery table to evaluate the condition and compute aggregates. Space complexity is O(1) since only aggregate counters are stored during execution.

Ready to solve this problem?

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

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance68.2%

Approaches1

Reading time4 min

Practice this problem

Open in Editor