Daily Immediate Delivery Share

Medium

A logistics analytics team tracks whether customers request same-day fulfillment when placing orders. Table: Delivery delivery_id: unique order event identifier customer_id: customer identifier order_date: date when the order was placed customer_pref_delivery_date: customer's preferred delivery date Order classification: immediate order: customer_pref_delivery_date is the same day as order_date scheduled order: customer_pref_delivery_date is later than order_date Task: For each distinct order_date, compute the percentage of immediate orders among all orders placed on that date. Metric definition per order_date: immediate_percentage = (immediate_orders / total_orders) * 100 Output requirements: Return exactly two columns: order_date, immediate_percentage Round immediate_percentage to 2 decimal places Return rows sorted by order_date in ascending order If Delivery is empty, return an empty table Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Delivery:
| delivery_id | customer_id | order_date  | customer_pref_delivery_date |
|-------------|-------------|-------------|-----------------------------|
| 1           | 11          | 2024-03-01  | 2024-03-01                  |
| 2           | 12          | 2024-03-01  | 2024-03-03                  |
| 3           | 13          | 2024-03-01  | 2024-03-01                  |
| 4           | 12          | 2024-03-02  | 2024-03-02                  |
| 5           | 14          | 2024-03-02  | 2024-03-05                  |
| 6           | 15          | 2024-03-03  | 2024-03-03                  |

Output

[
  {"order_date":"2024-03-01","immediate_percentage":66.67},
  {"order_date":"2024-03-02","immediate_percentage":50.0},
  {"order_date":"2024-03-03","immediate_percentage":100.0}
]

Explanation

On 2024-03-01, 2 of 3 orders are immediate. On 2024-03-02, 1 of 2 is immediate. On 2024-03-03, 1 of 1 is immediate.

Example 2

Input

Delivery:
| delivery_id | customer_id | order_date  | customer_pref_delivery_date |
|-------------|-------------|-------------|-----------------------------|
| 100         | 8           | 2024-01-10  | 2024-01-15                  |
| 101         | 9           | 2024-01-10  | 2024-01-20                  |
| 102         | 9           | 2024-01-11  | 2024-01-11                  |
| 103         | 7           | 2024-01-11  | 2024-01-12                  |

Output

[
  {"order_date":"2024-01-10","immediate_percentage":0.0},
  {"order_date":"2024-01-11","immediate_percentage":50.0}
]

Explanation

The first date has no immediate requests. The second date has one immediate order out of two total orders.

Example 3

Input

Delivery:
[]

Output

[]

Explanation

There are no order dates to report, so the result is empty.

Constraints

0 <= number of rows in Delivery <= 200000
delivery_id values are unique
1 <= delivery_id <= 2147483647
1 <= customer_id <= 2147483647
order_date and customer_pref_delivery_date are valid YYYY-MM-DD dates
For every row: customer_pref_delivery_date >= order_date
Return exactly columns: order_date, immediate_percentage
Return one row per distinct order_date
Round immediate_percentage to exactly 2 decimal places
Sort the output by order_date ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: order_date, immediate_percentage

Delivery: | delivery_id | customer_id | order_date | customer_pref_delivery_date | |-------------|-------------|-------------|-----------------------------| | 1 | 11 | 2024-03-01 | 2024-03-01 | | 2 | 12 | 2024-03-01 | 2024-03-03 | | 3 | 13 | 2024-03-01 | 2024-03-01 | | 4 | 12 | 2024-03-02 | 2024-03-02 | | 5 | 14 | 2024-03-02 | 2024-03-05 | | 6 | 15 | 2024-03-03 | 2024-03-03 |

Delivery: | delivery_id | customer_id | order_date | customer_pref_delivery_date | |-------------|-------------|-------------|-----------------------------| | 100 | 8 | 2024-01-10 | 2024-01-15 | | 101 | 9 | 2024-01-10 | 2024-01-20 | | 102 | 9 | 2024-01-11 | 2024-01-11 | | 103 | 7 | 2024-01-11 | 2024-01-12 |