Immediate Food Delivery III - Practice Coding Problems

Immediate Food Delivery III - Problem

Database Medium

Immediate Food Delivery III

You're working as a data analyst for a popular food delivery platform! 🍕📊

The company wants to understand their delivery patterns by analyzing what percentage of orders are immediate versus scheduled on each day.

Problem Setup:
You have a Delivery table with the following structure:

Column Name	Type
delivery_id	int
customer_id	int
order_date	date
customer_pref_delivery_date	date

Key Definitions:
• Immediate Order: When customer_pref_delivery_date = order_date
• Scheduled Order: When customer_pref_delivery_date > order_date

Your Task:
Calculate the percentage of immediate orders for each unique order_date, rounded to 2 decimal places. Return results ordered by order_date ascending.

Expected Output Format:
order_date | immediate_percentage

Input & Output

example_1.sql — Basic Example

$ 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 | | 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 2019-08-01: 0 immediate out of 1 total = 0%. For 2019-08-02: 1 immediate out of 1 total = 100%. For 2019-08-09: 1 immediate out of 1 total = 100%. For 2019-08-11: 0 immediate out of 2 total = 0%. For 2019-08-21: 0 immediate out of 1 total = 0%. For 2019-08-24: 1 immediate out of 1 total = 100%.

example_2.sql — Mixed Percentages

$ Input: Delivery 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-02 | 2020-01-03 | | 5 | 5 | 2020-01-02 | 2020-01-02 | | 6 | 6 | 2020-01-02 | 2020-01-04 |

› Output: | order_date | immediate_percentage | |------------|---------------------| | 2020-01-01 | 66.67 | | 2020-01-02 | 33.33 |

💡 Note: For 2020-01-01: 2 immediate orders (delivery_id 1,3) out of 3 total = 66.67%. For 2020-01-02: 1 immediate order (delivery_id 5) out of 3 total = 33.33%.

example_3.sql — Edge Case: All Scheduled

› Output: | order_date | immediate_percentage | |------------|---------------------| | 2023-12-25 | 0.00 | | 2023-12-26 | 100.00 |

💡 Note: For 2023-12-25: 0 immediate out of 2 total = 0.00% (both orders scheduled for later). For 2023-12-26: 1 immediate out of 1 total = 100.00%.

Visualization

Tap to expand

Understanding the Visualization

Data Collection

Orders flow in throughout each day with delivery preferences

Daily Grouping

Group all orders by their order date using GROUP BY

Conditional Counting

Use CASE statements to count immediate vs total orders simultaneously

Percentage Calculation

Calculate and round percentages for each day

Report Generation

Sort by date and generate the final analytics report

Key Takeaway

🎯 Key Insight: GROUP BY with conditional aggregation (CASE WHEN) allows us to count different types of records and calculate percentages in a single, efficient database operation.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all n records, with GROUP BY typically O(n) with hash-based grouping

✓ Linear Growth

Space Complexity

O(d)

Space for d unique dates in GROUP BY result and hash table

✓ Linear Space

Constraints

1 ≤ delivery_id ≤ 10⁵
1 ≤ customer_id ≤ 10⁴
order_date and customer_pref_delivery_date are valid dates
customer_pref_delivery_date ≥ order_date (preferred delivery cannot be before order date)
All delivery_id values are unique
Result percentages must be rounded to exactly 2 decimal places

Asked in

D DoorDash 85 U Uber 72 a Amazon 45 G Grubhub 38

The optimal solution uses GROUP BY with conditional aggregation to calculate immediate delivery percentages in a single table scan. Key insights: SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matching records, COUNT(*) gives total records per group, and ROUND(..., 2) ensures proper decimal formatting. Time complexity: O(n) with efficient database grouping.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery for Each Date	O(n²)	O(m)	Calculate total and immediate orders separately for each date using subqueries
GROUP BY with Conditional Aggregation (Optimal)	O(n)	O(d)	Use GROUP BY and conditional aggregation to calculate percentages in a single table scan

Subquery for Each Date — Algorithm Steps

Get all unique order dates
For each date, count total orders with a subquery
For each date, count immediate orders with another subquery
Calculate percentage and round to 2 decimal places
Order results by date

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Unique Dates

Scan table to collect all unique order_date values

For Each Date

Scan entire table again to count total orders for this date

Scan Again

Another full table scan to count immediate orders for this date

Calculate

Compute percentage: (immediate/total) * 100

Code -

solution.c — C

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

#define MAX_DELIVERIES 1000
#define MAX_DATE_LEN 12

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

typedef struct {
    char order_date[MAX_DATE_LEN];
    double immediate_percentage;
} Result;

int compare_dates(const void *a, const void *b) {
    return strcmp((char*)a, (char*)b);
}

int calculateImmediatePercentage(Delivery deliveries[], int n, Result results[]) {
    // Get unique dates
    char unique_dates[MAX_DELIVERIES][MAX_DATE_LEN];
    int unique_count = 0;
    
    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < unique_count; j++) {
            if (strcmp(deliveries[i].order_date, unique_dates[j]) == 0) {
                found = 1;
                break;
            }
        }
        if (!found) {
            strcpy(unique_dates[unique_count], deliveries[i].order_date);
            unique_count++;
        }
    }
    
    // Sort unique dates
    qsort(unique_dates, unique_count, MAX_DATE_LEN, compare_dates);
    
    // Brute force: for each date, scan entire array
    for (int i = 0; i < unique_count; i++) {
        int total = 0, immediate = 0;
        
        for (int j = 0; j < n; j++) {
            if (strcmp(deliveries[j].order_date, unique_dates[i]) == 0) {
                total++;
                if (strcmp(deliveries[j].customer_pref_delivery_date, 
                          deliveries[j].order_date) == 0) {
                    immediate++;
                }
            }
        }
        
        strcpy(results[i].order_date, unique_dates[i]);
        results[i].immediate_percentage = round((double)immediate / total * 100 * 100.0) / 100.0;
    }
    
    return unique_count;
}

const char* getSQLQuery() {
    return "SELECT d1.order_date, "
           "ROUND((SELECT COUNT(*) FROM Delivery d2 "
           "WHERE d2.order_date = d1.order_date "
           "AND d2.customer_pref_delivery_date = d2.order_date) * 100.0 / "
           "(SELECT COUNT(*) FROM Delivery d3 "
           "WHERE d3.order_date = d1.order_date), 2) AS immediate_percentage "
           "FROM (SELECT DISTINCT order_date FROM Delivery) d1 "
           "ORDER BY d1.order_date;";
}

int main() {
    printf("%s\n", getSQLQuery());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each unique date (m dates), we scan all n records multiple times

⚠ Quadratic Growth

Space Complexity

O(m)

Space for m unique dates in result set

✓ Linear Space

Constraints

1 ≤ delivery_id ≤ 10⁵
1 ≤ customer_id ≤ 10⁴
order_date and customer_pref_delivery_date are valid dates
customer_pref_delivery_date ≥ order_date (preferred delivery cannot be before order date)
All delivery_id values are unique
Result percentages must be rounded to exactly 2 decimal places

94.5K Views

High Frequency

~15 min Avg. Time

2.3K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Subquery for Each Date — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler