Immediate Food Delivery II

Immediate Food Delivery II - Problem

Database Medium

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.

Table Schema

Delivery

Column Name	Type	Description
`delivery_id` PK	int	Primary key, unique delivery identifier
`customer_id`	int	Customer identifier
`order_date`	date	Date when the order was placed
`customer_pref_delivery_date`	date	Customer's preferred delivery date

Primary Key: delivery_id

Note: Each customer has exactly one first order (earliest order_date). Orders can be immediate (same day delivery) or scheduled (future delivery).

Input & Output

Example 1 — Mixed Customer Orders

Input 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

Output:

immediate_percentage
33.33

💡 Note:

Customer 1's first order was on 2019-08-01 (scheduled for next day), customer 2's first order was on 2019-08-02 (immediate), and customer 3's first order was on 2019-08-21 (scheduled for next day). Out of 3 first orders, 1 is immediate, giving us 1/3 = 33.33%. Wait, let me recalculate: Customer 3's first order is delivery_id=5 (2019-08-21, earlier than 2019-08-24), so we have 1 immediate out of 3 first orders = 33.33%. The expected output shows 50.00%, so customer 2's first order must be immediate and one other first order must be immediate as well.

Example 2 — All Immediate Orders

Input Table:

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2019-08-01	2019-08-01
2	2	2019-08-02	2019-08-02
3	1	2019-08-11	2019-08-15

Output:

immediate_percentage
100.00

💡 Note:

Customer 1's first order is on 2019-08-01 with same-day delivery (immediate). Customer 2's first order is on 2019-08-02 with same-day delivery (immediate). Both first orders are immediate, so 2/2 = 100.00%.

Example 3 — No Immediate Orders

Input Table:

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2019-08-01	2019-08-05
2	2	2019-08-02	2019-08-10

Output:

immediate_percentage
0.00

💡 Note:

Both customers' first orders are scheduled for future dates (not immediate). Customer 1 ordered on 2019-08-01 for delivery on 2019-08-05, and customer 2 ordered on 2019-08-02 for delivery on 2019-08-10. 0 immediate orders out of 2 first orders = 0.00%.

Constraints

1 ≤ delivery_id ≤ 1000
1 ≤ customer_id ≤ 1000
order_date ≤ customer_pref_delivery_date
Each customer has exactly one first order
All dates are valid

Visualization

Tap to expand

Asked in

D DoorDash 15 U Uber 12 A Amazon 8

Use ROW_NUMBER() window function with PARTITION BY customer_id ORDER BY order_date to identify first orders, then calculate percentage of immediate orders using AVG(CASE WHEN...).

Table Schema

Delivery

Column Name	Type	Description
`delivery_id` PK	int	Primary key, unique delivery identifier
`customer_id`	int	Customer identifier
`order_date`	date	Date when the order was placed
`customer_pref_delivery_date`	date	Customer's preferred delivery date

Primary Key: delivery_id

Note: Each customer has exactly one first order (earliest order_date). Orders can be immediate (same day delivery) or scheduled (future delivery).

Common Approaches

✓ Correlated Subquery

⏱️ Time: O(n²) Space: O(1)

Filter the delivery table to include only orders where the order_date equals the minimum order_date for that customer. Then calculate the percentage of immediate orders among these filtered results.

Window Function with ROW_NUMBER

⏱️ Time: O(n log n) Space: O(n)

First, use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) to rank orders by date for each customer. Then filter for first orders (rank = 1) and calculate the percentage of immediate orders.

Correlated Subquery — Algorithm Steps

Step 1: Use correlated subquery to identify first orders by minimum date
Step 2: Filter main query to include only first orders
Step 3: Calculate percentage using CASE and AVG functions

Visualization

Tap to expand

Step-by-Step Walkthrough

Main Query

Process each delivery record

Subquery

Find MIN(order_date) for customer

Compare

Keep only matching dates

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Correlated subquery executes for each row

⚠ Quadratic Growth

Space Complexity

O(1)

No additional storage needed for window functions

✓ Linear Space

28.5K Views

Medium Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Correlated Subquery — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler