Immediate Food Delivery I - Practice Coding Problems

Immediate Food Delivery I - Problem

Database Easy

Table: Delivery

Column Name	Type
delivery_id	int
customer_id	int
order_date	date
customer_pref_delivery_date	date

delivery_id is the primary key of this table. The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date.

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.

Write a solution to find the percentage of immediate orders in the table, 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: Orders are immediate if order_date equals customer_pref_delivery_date

Input & Output

Example 1 — Mixed Immediate and Scheduled 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:

Out of 6 total orders, only orders with delivery_id 2 and 4 are immediate (order_date = customer_pref_delivery_date). So 2/6 = 33.33%.

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

Output:

immediate_percentage
100

💡 Note:

Both orders are immediate since order_date equals customer_pref_delivery_date for all orders. Result is 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-02
2	2	2019-08-02	2019-08-05

Output:

immediate_percentage
0

💡 Note:

No orders are immediate since all have different order_date and customer_pref_delivery_date. Result is 0.00%.

Constraints

1 ≤ delivery_id ≤ 500
1 ≤ customer_id ≤ 100
order_date and customer_pref_delivery_date are valid dates
The table contains at least one row

Visualization

Tap to expand

Understanding the Visualization

Input

Delivery table with order and preferred delivery dates

CASE WHEN

Assign 100.0 for immediate orders, 0.0 for scheduled

AVG & ROUND

Calculate average percentage and round to 2 decimals

Key Takeaway

🎯 Key Insight: Use CASE WHEN with AVG to calculate percentages in a single SQL query

Asked in

D DoorDash 15 U Uber 8

Use conditional aggregation with CASE WHEN to identify immediate orders, then AVG() to calculate percentage and ROUND() for 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: Orders are immediate if order_date equals customer_pref_delivery_date

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation with CASE	O(n)	O(1)	Use CASE WHEN to count immediate orders and calculate percentage

Conditional Aggregation with CASE — Algorithm Steps

Step 1: Use CASE WHEN to identify immediate orders
Step 2: Calculate average and multiply by 100 for percentage
Step 3: Round result to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Compare Dates

Check if order_date = customer_pref_delivery_date

Assign Values

100.0 for immediate, 0.0 for scheduled

Calculate Average

AVG gives percentage, ROUND to 2 decimals

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all delivery records

✓ Linear Growth

Space Complexity

O(1)

Only stores aggregate results

✓ Linear Space

35.0K Views

Medium Frequency

~8 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

Conditional Aggregation with CASE — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler