Immediate Food Delivery I - Practice Coding Problems

Immediate Food Delivery I - Problem

Database Easy

🍕 Food Delivery Analysis: Immediate vs Scheduled Orders

You're working as a data analyst for a popular food delivery platform! The company wants to understand customer ordering behavior by analyzing their delivery preferences.

You have access to the Delivery table with the following structure:

Column Name	Type
delivery_id	int
customer_id	int
order_date	date
customer_pref_delivery_date	date

Business Rules:

📦 Immediate Order: When customer_pref_delivery_date equals order_date
📅 Scheduled Order: When customer_pref_delivery_date is after order_date

Your mission: Calculate the percentage of immediate orders in the table, rounded to 2 decimal places. This metric helps the business understand how many customers want their food delivered immediately versus planning ahead!

Input & Output

basic_example.sql — Standard Case

$ 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-02 | 2019-08-02 | | 3 | 1 | 2019-08-11 | 2019-08-12 | | 4 | 3 | 2019-08-24 | 2019-08-24 | +-------------+-------------+------------+-----------------------------+

› Output: 50.00

💡 Note: Out of 4 total orders, 2 are immediate (delivery_id 2 and 4 have matching dates). Percentage = (2/4) × 100 = 50.00%

all_immediate.sql — All Immediate Orders

$ Input: Delivery 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: 100.00

💡 Note: All 2 orders are immediate (both have matching order and preferred delivery dates). Percentage = (2/2) × 100 = 100.00%

no_immediate.sql — No Immediate Orders

$ Input: Delivery table: +-------------+-------------+------------+-----------------------------+ | delivery_id | customer_id | order_date | customer_pref_delivery_date | +-------------+-------------+------------+-----------------------------+ | 1 | 1 | 2019-08-01 | 2019-08-03 | | 2 | 2 | 2019-08-02 | 2019-08-05 | +-------------+-------------+------------+-----------------------------+

› Output: 0.00

💡 Note: No orders are immediate (all preferred delivery dates are after order dates). Percentage = (0/2) × 100 = 0.00%

Visualization

Tap to expand

Understanding the Visualization

Order Intake

Each order comes in with order date and preferred delivery date

Smart Classification

System automatically compares dates: same date = immediate, different date = scheduled

Real-time Counting

Dashboard maintains running count of immediate vs total orders

Percentage Display

Business metric updates live: (immediate_count ÷ total_count) × 100

Key Takeaway

🎯 Key Insight: Use conditional aggregation in SQL to count specific cases and calculate percentages in a single, efficient query that scales well with large datasets.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all delivery records

✓ Linear Growth

Space Complexity

O(1)

Only storing aggregate counters

✓ Linear Space

Constraints

1 ≤ Number of delivery records ≤ 500
delivery_id is the primary key
customer_pref_delivery_date is on the same day or after order_date
Result must be rounded to exactly 2 decimal places

Asked in

DD DoorDash 45 U Uber 38 a Amazon 32 GH Grubhub 28

The optimal solution uses a single SQL query with conditional aggregation. Count immediate orders using CASE WHEN statement, divide by total count, multiply by 100, and ROUND to 2 decimal places. Time complexity is O(n) with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Basic SQL Query with Conditional Logic	O(n)	O(1)	Use CASE statements to classify each order and calculate percentage

Basic SQL Query with Conditional Logic — Algorithm Steps

Scan through each delivery record
Use CASE statement to identify immediate orders (order_date = customer_pref_delivery_date)
Count total immediate orders and total orders
Calculate percentage using basic arithmetic
Round result to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Orders

Go through each delivery record

Compare Dates

Check if order_date equals customer_pref_delivery_date

Classify

Mark as immediate (✓) or scheduled (✗)

Calculate

Compute percentage: (immediate_count / total_count) × 100

Code -

solution.c — C

#include <stdio.h>
#include <string.h>
#include <math.h>

typedef struct {
    int delivery_id;
    char order_date[11];
    char customer_pref_delivery_date[11];
} Delivery;

double calculateImmediatePercentage(Delivery* deliveries, int count) {
    if (count == 0) {
        return 0.00;
    }
    
    int immediate_count = 0;
    
    // Count immediate orders
    for (int i = 0; i < count; i++) {
        if (strcmp(deliveries[i].order_date, deliveries[i].customer_pref_delivery_date) == 0) {
            immediate_count++;
        }
    }
    
    // Calculate percentage
    double percentage = (immediate_count * 100.0) / count;
    return round(percentage * 100.0) / 100.0;
}

int main() {
    Delivery deliveries[] = {
        {1, "2019-08-01", "2019-08-02"},
        {2, "2019-08-02", "2019-08-02"},
        {3, "2019-08-11", "2019-08-12"}
    };
    
    int count = sizeof(deliveries) / sizeof(deliveries[0]);
    double result = calculateImmediatePercentage(deliveries, count);
    
    printf("%.2f%%\n", result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all delivery records

✓ Linear Growth

Space Complexity

O(1)

Only storing aggregate counters

✓ Linear Space

Constraints

1 ≤ Number of delivery records ≤ 500
delivery_id is the primary key
customer_pref_delivery_date is on the same day or after order_date
Result must be rounded to exactly 2 decimal places

28.4K Views

High 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

🍕 Food Delivery Analysis: Immediate vs Scheduled Orders

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic SQL Query with Conditional Logic — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler