LeetCode 1173 - Immediate Food Delivery I

The problem is asking to calculate the percentage of immediate food deliveries from a delivery table. An immediate delivery is defined as one where the customerprefdeliverydate matches the orderdate.

leetcodeeasydatabase

LeetCode Problem 1173

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem is asking to calculate the percentage of immediate food deliveries from a delivery table. An immediate delivery is defined as one where the customer_pref_delivery_date matches the order_date. The input is a table named Delivery with columns delivery_id, customer_id, order_date, and customer_pref_delivery_date. Each row represents a single delivery order with its preferred delivery date.

The expected output is a single value showing the percentage of immediate deliveries, rounded to 2 decimal places. The constraints indicate that delivery_id is unique, dates are valid, and all orders have a preferred delivery date on or after the order date. The table may contain any number of rows, but since this is a SQL problem, performance considerations mainly involve aggregate computations rather than iterative loops.

Important edge cases include orders where all deliveries are immediate, all are scheduled, or there are zero deliveries in the table. Another subtlety is ensuring proper decimal rounding to exactly two decimal places.

Approaches

A brute-force approach would involve scanning the table row by row, counting how many orders are immediate and dividing by the total number of orders. In SQL, this translates to counting the rows where order_date = customer_pref_delivery_date, counting the total rows, and then calculating the ratio. This is correct but can be slightly verbose.

The optimal approach leverages SQL aggregation functions in a single query. By using the COUNT function with a CASE expression, we can directly count immediate deliveries and total deliveries, then compute the ratio in one step. Applying the ROUND function ensures proper decimal formatting.

Approach Time Complexity Space Complexity Notes
Brute Force O(n) O(1) Scan the table twice or use multiple queries
Optimal O(n) O(1) Single query with aggregation and rounding

Algorithm Walkthrough

  1. Use the COUNT(*) function to calculate the total number of rows in the Delivery table. This represents all orders placed.
  2. Use COUNT with a CASE expression to count only the immediate deliveries, i.e., rows where order_date = customer_pref_delivery_date.
  3. Compute the percentage by dividing the immediate count by the total count, multiplying by 100 to convert to percentage.
  4. Apply the ROUND function with 2 as the precision to ensure the output matches the required format.
  5. Return the result with the alias immediate_percentage.

Why it works: This approach works because SQL aggregates can efficiently scan the table once to produce both counts. Using the CASE expression ensures that only immediate deliveries contribute to the numerator, while the total count forms the denominator. Multiplying by 100 converts the fraction to a percentage, and rounding guarantees the exact two-decimal output.

Python Solution

Although this is primarily a SQL problem, in Python you could achieve similar logic by querying the database with SQL or using pandas. For completeness, here is a Python equivalent using pandas:

import pandas as pd

def immediate_percentage(delivery: pd.DataFrame) -> float:
    total_orders = len(delivery)
    if total_orders == 0:
        return 0.0
    immediate_orders = delivery[delivery['order_date'] == delivery['customer_pref_delivery_date']]
    percentage = (len(immediate_orders) / total_orders) * 100
    return round(percentage, 2)

This function calculates the percentage of immediate deliveries and rounds the result to two decimal places.

Go Solution

Since this problem is SQL-focused, there is no typical Go solution that processes raw SQL tables. If the table is represented as a slice of structs, the logic would mirror the Python solution.

package main

import (
    "fmt"
    "time"
)

type Delivery struct {
    DeliveryID                int
    CustomerID                int
    OrderDate                 string
    CustomerPrefDeliveryDate  string
}

func immediatePercentage(deliveries []Delivery) float64 {
    total := len(deliveries)
    if total == 0 {
        return 0.0
    }
    count := 0
    for _, d := range deliveries {
        if d.OrderDate == d.CustomerPrefDeliveryDate {
            count++
        }
    }
    percentage := (float64(count) / float64(total)) * 100
    return float64(int(percentage*100+0.5)) / 100 // round to 2 decimals
}

func main() {
    deliveries := []Delivery{
        {1, 1, "2019-08-01", "2019-08-02"},
        {2, 5, "2019-08-02", "2019-08-02"},
        {3, 1, "2019-08-11", "2019-08-11"},
        {4, 3, "2019-08-24", "2019-08-26"},
        {5, 4, "2019-08-21", "2019-08-22"},
        {6, 2, "2019-08-11", "2019-08-13"},
    }
    fmt.Println(immediatePercentage(deliveries)) // Output: 33.33
}

In Go, we manually iterate over the slice of deliveries, count immediate deliveries, calculate the ratio, and round to two decimal places using integer arithmetic to avoid floating-point inaccuracies.

Worked Examples

Example 1:

Input: Delivery table with 6 rows as given
Immediate orders: delivery_id 2, 3
Total orders: 6
Percentage = (2 / 6) * 100 = 33.3333
Rounded to 2 decimals: 33.33

Example 2: All immediate

Input: delivery_id 1, 2, 3 all have order_date = customer_pref_delivery_date
Immediate orders: 3
Total orders: 3
Percentage = (3 / 3) * 100 = 100.0

Example 3: None immediate

Input: delivery_id 1, 2, 3 none have matching dates
Immediate orders: 0
Total orders: 3
Percentage = 0.0

Complexity Analysis

Measure Complexity Explanation
Time O(n) Single scan over the table to count immediate and total orders
Space O(1) Only simple counters needed; no extra data structures

The reasoning is straightforward: we only need a single pass to compute aggregates, which is highly efficient for even large tables.

Test Cases

import pandas as pd

# Basic example
df1 = pd.DataFrame([
    [1, 1, "2019-08-01", "2019-08-02"],
    [2, 5, "2019-08-02", "2019-08-02"],
    [3, 1, "2019-08-11", "2019-08-11"],
    [4, 3, "2019-08-24", "2019-08-26"],
    [5, 4, "2019-08-21", "2019-08-22"],
    [6, 2, "2019-08-11", "2019-08-13"],
], columns=['delivery_id', 'customer_id', 'order_date', 'customer_pref_delivery_date'])
assert immediate_percentage(df1) == 33.33  # mixed orders

# All immediate
df2 = pd.DataFrame([
    [1, 1, "2020-01-01", "2020-01-01"],
    [2, 2, "2020-01-02", "2020-01-02"]
], columns=['delivery_id', 'customer_id', 'order_date', 'customer_pref_delivery_date'])
assert immediate_percentage(df2) == 100.00  # all immediate

# None immediate
df3 = pd.DataFrame([
    [1, 1, "2020-01-01", "2020-01-02"],
    [2, 2, "2020-01-02", "2020-01-03"]
], columns=['delivery_id', 'customer_id', 'order_date', 'customer_pref_delivery_date'])
assert immediate_percentage(df3) == 0.0  # all scheduled

# Empty table
df4 = pd.DataFrame(columns=['delivery_id', 'customer_id', 'order_date', 'customer_pref_delivery_date'])
assert immediate_percentage(df4) == 0.0  # no orders
Test Why
Mixed orders Validates normal computation of immediate percentage
All immediate Validates 100% case and rounding
None immediate Validates 0% case
Empty table Ensures division by zero is handled safely

Edge Cases

The first edge case is when the table is empty. Without handling, dividing by zero would raise an error. The implementation explicitly checks if total_orders == 0 and returns 0.0.

The second edge case is when all deliveries are immediate or all are scheduled. This tests that the algorithm correctly handles extremes of 0% or 100%, and that rounding is consistent.

The third edge case involves rounding behavior. For example, if the computed percentage is 33.3333, rounding must produce exactly 33.33. The implementation uses the round function in Python and integer arithmetic in Go to ensure precise two-decimal output, avoiding floating-point inconsistencies.