Immediate Food Delivery III - Problem

Table: Delivery

Column NameType
delivery_idint
customer_idint
order_datedate
customer_pref_delivery_datedate

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
Immediate Food Delivery III INPUT order_id order_dt pref_dt 1 2024-01-01 2024-01-01 2 2024-01-01 2024-01-02 3 2024-01-01 2024-01-01 4 2024-01-02 2024-01-02 5 2024-01-02 2024-01-03 Immediate (dates match) Scheduled (dates differ) Data Summary Total Orders: 5 Unique Dates: 2 Immediate: 3 Scheduled: 2 ALGORITHM STEPS 1 Group by order_date Partition orders by date 2 Count total per date COUNT(*) for each group 3 Count immediate orders SUM(order_dt = pref_dt) 4 Calculate percentage ROUND(imm/total*100, 2) Calculation Table Date Total Imm % 01-01 3 2 66.67 01-02 2 1 50.00 (2/3)*100 = 66.67 FINAL RESULT order_date pct 2024-01-01 66.67 2024-01-02 50.00 Immediate Order Rate 01-01 66.67% 01-02 50.00% Output Format [ {"date":"01-01","pct":66.67}, {"date":"01-02","pct":50.00} ] Key Insight: Use GROUP BY order_date with conditional aggregation: SUM(CASE WHEN order_date = preferred_delivery_date THEN 1 ELSE 0 END) / COUNT(*) * 100. ROUND to 2 decimals. This calculates percentage per date in one pass. Time: O(n), Space: O(unique dates). Optimal solution avoids multiple table scans. TutorialsPoint - Immediate Food Delivery III | Optimal Solution

Related Problems

Asked in
DoorDash 15 Uber 12 Amazon 8
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