Immediate Food Delivery II
Preview mode. Log in to edit, run, submit, and save progress.
Description
You have a Delivery table. Each delivery has an order_date and a customer_pref_delivery_date. A delivery is considered 'immediate' if the order_date equals the customer_pref_delivery_date; otherwise it is 'scheduled'. Each customer's first order is the one with the minimum order_date. Write a SQL query to find the percentage of customers whose first order was immediate. Round to 2 decimal places.
Database Schema
Delivery
| Column Name | Type | Description |
|---|---|---|
| delivery_id | INT | Primary key |
| customer_id | INT | Customer identifier |
| order_date | DATE | Date order was placed |
| customer_pref_delivery_date | DATE | Customer's preferred delivery date |
Example
Delivery
| 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
| immediate_percentage |
|---|
| 50 |
Explanation:
Customer 1's first order (id=1) is scheduled. Customer 2's first order (id=2) is immediate. Customer 3's first order (id=5) is scheduled. Customer 4's first order (id=7) is immediate. 2 out of 4 = 50.00%.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Delivery
| 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
| immediate_percentage |
|---|
| 50 |