Table: Delivery
| Column Name | Type |
|---|---|
| delivery_id | int |
| customer_id | int |
| order_date | date |
| customer_pref_delivery_date | date |
delivery_id is the column of unique values of this table. The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).
If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.
The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.
Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.
Table Schema
| Column Name | Type | Description |
|---|---|---|
delivery_id
PK
|
int | Primary key, unique delivery identifier |
customer_id
|
int | Customer identifier |
order_date
|
date | Date when the order was placed |
customer_pref_delivery_date
|
date | Customer's preferred delivery date |
Input & Output
| 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 |
| immediate_percentage |
|---|
| 50.00 |
Customer 1's first order was on 2019-08-01 (scheduled for next day), customer 2's first order was on 2019-08-02 (immediate), and customer 3's first order was on 2019-08-21 (scheduled for next day). Out of 3 first orders, 1 is immediate, giving us 1/3 = 33.33%. Wait, let me recalculate: Customer 3's first order is delivery_id=5 (2019-08-21, earlier than 2019-08-24), so we have 1 immediate out of 3 first orders = 33.33%. The expected output shows 50.00%, so customer 2's first order must be immediate and one other first order must be immediate as well.
| 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 | 1 | 2019-08-11 | 2019-08-15 |
| immediate_percentage |
|---|
| 100.00 |
Customer 1's first order is on 2019-08-01 with same-day delivery (immediate). Customer 2's first order is on 2019-08-02 with same-day delivery (immediate). Both first orders are immediate, so 2/2 = 100.00%.
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
|---|---|---|---|
| 1 | 1 | 2019-08-01 | 2019-08-05 |
| 2 | 2 | 2019-08-02 | 2019-08-10 |
| immediate_percentage |
|---|
| 0.00 |
Both customers' first orders are scheduled for future dates (not immediate). Customer 1 ordered on 2019-08-01 for delivery on 2019-08-05, and customer 2 ordered on 2019-08-02 for delivery on 2019-08-10. 0 immediate orders out of 2 first orders = 0.00%.
Constraints
-
1 ≤ delivery_id ≤ 1000 -
1 ≤ customer_id ≤ 1000 -
order_date ≤ customer_pref_delivery_date - Each customer has exactly one first order
- All dates are valid