Immediate Food Delivery III - Practice Coding Problems

Immediate Food Delivery III - 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 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.

Table Schema

Delivery

Column Name	Type	Description
`delivery_id` PK	int	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 delivery has a unique ID. Orders are immediate if preferred delivery date equals order date, otherwise scheduled.

Input & Output

Example 1 — Basic Percentage Calculation

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
7	4	2019-08-09	2019-08-09

Output:

order_date	immediate_percentage
2019-08-01	0.00
2019-08-02	100.00
2019-08-09	100.00
2019-08-11	0.00
2019-08-21	0.00
2019-08-24	100.00

💡 Note:

For each order date, we calculate the percentage of immediate orders. For example, on 2019-08-01, there's 1 order but it's scheduled (0% immediate). On 2019-08-02, there's 1 order and it's immediate (100%). On 2019-08-11, there are 2 orders but both are scheduled (0% immediate).

Example 2 — Mixed Immediate and Scheduled Orders

Input Table:

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2020-01-01	2020-01-01
2	2	2020-01-01	2020-01-02
3	3	2020-01-01	2020-01-01
4	4	2020-01-01	2020-01-03

Output:

order_date	immediate_percentage
2020-01-01	50.00

💡 Note:

On 2020-01-01, there are 4 total orders. 2 orders are immediate (delivery_id 1 and 3) and 2 are scheduled (delivery_id 2 and 4). This gives us 2/4 = 0.5 = 50.00% immediate orders.

Constraints

1 ≤ delivery_id ≤ 500
1 ≤ customer_id ≤ 100
order_date and customer_pref_delivery_date are valid dates
customer_pref_delivery_date is on or after order_date

Visualization

Tap to expand

Understanding the Visualization

Input

Delivery table with order dates and preferred dates

Group & Count

GROUP BY order_date with conditional counting

Output

Percentage of immediate orders per date

Key Takeaway

🎯 Key Insight: Use conditional aggregation to calculate percentages efficiently in SQL GROUP BY queries

Asked in

D DoorDash 15 U Uber 12 A Amazon 8

Use GROUP BY with order_date and conditional aggregation (CASE WHEN inside SUM) to count immediate orders and calculate percentage efficiently.

Table Schema

Delivery

Column Name	Type	Description
`delivery_id` PK	int	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 delivery has a unique ID. Orders are immediate if preferred delivery date equals order date, otherwise scheduled.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with Conditional Aggregation	O(n)	O(d)	Group by order date and use conditional counting to calculate immediate order percentage

GROUP BY with Conditional Aggregation — Algorithm Steps

Step 1: Group all deliveries by order_date
Step 2: Use conditional SUM to count immediate orders (where order_date = customer_pref_delivery_date)
Step 3: Calculate percentage and round to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group by order_date

Count

Conditional count of immediate orders

Calculate

Percentage with rounding

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all delivery records

✓ Linear Growth

Space Complexity

O(d)

Space for unique order dates in GROUP BY

✓ Linear Space

28.5K Views

Medium Frequency

~12 min Avg. Time

890 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

GROUP BY with Conditional Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler