Immediate Food Delivery II - Practice Coding Problems

Immediate Food Delivery II - Problem

Database Medium

Food Delivery Analytics Challenge

You're working as a data analyst for a food delivery company and need to analyze customer ordering patterns. The company wants to understand what percentage of customers place immediate orders on their very first order.

Given a Delivery table with the following structure:

Column Name	Type
delivery_id	int
customer_id	int
order_date	date
customer_pref_delivery_date	date

Order Classification:
• Immediate: When order_date = customer_pref_delivery_date
• Scheduled: When customer_pref_delivery_date > order_date

Your Task:
Calculate the percentage of immediate orders among all customers' first orders only. The result should be rounded to 2 decimal places.

💡 Key Point: You only need to consider each customer's earliest order date, not all their orders.

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 | +-------------+-------------+------------+-----------------------------+

› Output: +--------------------+ | immediate_percentage| +--------------------+ | 50.00 | +--------------------+

💡 Note: Customer 1's first order was on 2019-08-01 (scheduled), Customer 2's first order was on 2019-08-02 (immediate), Customer 3's first order was on 2019-08-21 (scheduled). So 1 out of 3 first orders were immediate: (1/3) * 100 = 33.33%. Wait, let me recalculate: Customer 3's first order is 2019-08-21 (scheduled), but there's also delivery_id 4 on 2019-08-24 which is immediate. Since 2019-08-21 < 2019-08-24, the first order for customer 3 is scheduled. Actually, let me check again: Customer 2 and Customer 3's 2019-08-24 order are immediate, so 1 out of 3 = 33.33%. But the output shows 50.00, so there must be 1 immediate out of 2 first orders. Let me recheck: Customer 2 has immediate first order, Customer 3's first order (2019-08-21) is scheduled. So actually it's Customer 1 (scheduled), Customer 2 (immediate), Customer 3 (scheduled) = 1/3. The example seems to have an error, but typically 2 immediate out of 3 would be 66.67%, and 1 out of 2 would be 50.00%.

example_2.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 | | 3 | 3 | 2019-08-03 | 2019-08-03 | +-------------+-------------+------------+-----------------------------+

› Output: +--------------------+ | immediate_percentage| +--------------------+ | 100.00 | +--------------------+

💡 Note: All three customers have their first (and only) orders as immediate deliveries. All order dates match their preferred delivery dates, so 3 out of 3 first orders are immediate: (3/3) * 100 = 100.00%.

example_3.sql — Single Customer Multiple Orders

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

› Output: +--------------------+ | immediate_percentage| +--------------------+ | 0.00 | +--------------------+

💡 Note: Only one customer exists. Their first order was on 2019-08-01 with preferred delivery on 2019-08-02 (scheduled). The other orders don't matter since we only consider first orders. So 0 out of 1 first order is immediate: (0/1) * 100 = 0.00%.

Visualization

Tap to expand

Understanding the Visualization

Identify First Orders

Use window function to rank orders by date for each customer

Filter First Orders

Keep only records where row number equals 1

Check Immediate Status

Compare order date with preferred delivery date

Calculate Percentage

Count immediate orders and divide by total first orders

Key Takeaway

🎯 Key Insight: Using window functions with ROW_NUMBER() efficiently identifies first orders without expensive correlated subqueries, making the solution scalable for large datasets.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window function, then single pass through results

⚡ Linearithmic

Space Complexity

O(n)

CTE stores intermediate results with row numbers

⚡ Linearithmic Space

Constraints

1 ≤ Number of deliveries ≤ 10⁴
delivery_id is the primary key
Each customer has at least one order
order_date ≤ customer_pref_delivery_date
All dates are valid dates in YYYY-MM-DD format

Asked in

🚚 DoorDash 45 🚗 Uber 35 a Amazon 25 f Meta 20

The optimal approach uses a CTE with ROW_NUMBER() window function to efficiently identify each customer's first order, then calculates the percentage of immediate deliveries. This avoids expensive correlated subqueries and processes the data in O(n log n) time with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ CTE with Window Function (Optimized)	O(n log n)	O(n)	Use CTE with ROW_NUMBER() to identify first orders efficiently
Subquery Approach (Brute Force)	O(n²)	O(1)	Use correlated subqueries to find first orders for each customer

CTE with Window Function (Optimized) — Algorithm Steps

Create a CTE that assigns row numbers to orders, partitioned by customer and ordered by order_date
Filter the CTE to get only records where row_number = 1 (first orders)
Check if each first order is immediate (order_date = preferred_date)
Calculate the average and convert to percentage

Visualization

Tap to expand

Step-by-Step Walkthrough

Create CTE

Add ROW_NUMBER() partitioned by customer_id, ordered by order_date

Filter First Orders

Select only records where ROW_NUMBER() = 1

Check Immediate

Compare order_date with customer_pref_delivery_date

Calculate Average

Use AVG() on CASE statement results

Code -

solution.c — C

-- SQL Solution
WITH FirstOrders AS (
    SELECT 
        customer_id,
        order_date,
        customer_pref_delivery_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date ASC
        ) AS rn
    FROM Delivery
)
SELECT 
    ROUND(
        AVG(
            CASE WHEN order_date = customer_pref_delivery_date 
                 THEN 1.0 
                 ELSE 0.0 
            END
        ) * 100, 2
    ) AS immediate_percentage
FROM FirstOrders
WHERE rn = 1;

/* C simulation */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <math.h>

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

int compareDeliveries(const void *a, const void *b) {
    Delivery *da = (Delivery *)a;
    Delivery *db = (Delivery *)b;
    return strcmp(da->order_date, db->order_date);
}

double solution(Delivery* deliveries, int n) {
    // Step 1: Sort all deliveries by customer_id, then by order_date
    qsort(deliveries, n, sizeof(Delivery), 
          [](const void *a, const void *b) -> int {
              Delivery *da = (Delivery *)a;
              Delivery *db = (Delivery *)b;
              if (da->customer_id != db->customer_id) {
                  return da->customer_id - db->customer_id;
              }
              return strcmp(da->order_date, db->order_date);
          });
    
    // Step 2: Find first orders and calculate percentage
    int immediateCount = 0;
    int totalCustomers = 0;
    int lastCustomerId = -1;
    
    for (int i = 0; i < n; i++) {
        if (deliveries[i].customer_id != lastCustomerId) {
            // This is a first order for this customer
            totalCustomers++;
            if (strcmp(deliveries[i].order_date, 
                      deliveries[i].customer_pref_delivery_date) == 0) {
                immediateCount++;
            }
            lastCustomerId = deliveries[i].customer_id;
        }
    }
    
    return totalCustomers > 0 ? 
        round((double)immediateCount / totalCustomers * 100 * 100.0) / 100.0 : 0.0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window function, then single pass through results

⚡ Linearithmic

Space Complexity

O(n)

CTE stores intermediate results with row numbers

⚡ Linearithmic Space

Constraints

1 ≤ Number of deliveries ≤ 10⁴
delivery_id is the primary key
Each customer has at least one order
order_date ≤ customer_pref_delivery_date
All dates are valid dates in YYYY-MM-DD format

24.5K Views

High Frequency

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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

CTE with Window Function (Optimized) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler