Description
Write a SQL query to find the percentage of immediate orders in the first orders of all customers. An order is immediate if order_date = customer_pref_delivery_date. Only consider each customer's first order (earliest order_date). **Output columns (in order):** immediate_percentage (as a decimal, e.g., 50.0)
delivery| Column | Type |
|---|---|
| delivery_id | INT |
| customer_id | INT |
| order_date | TEXT (YYYY-MM-DD format) |
| customer_pref_delivery_date | TEXT (YYYY-MM-DD format) |
Examples
CREATE TABLE delivery (delivery_id INT, customer_id INT, order_date TEXT, customer_pref_delivery_date TEXT); INSERT INTO delivery VALUES (1, 1, '2024-01-01', '2024-01-01'), (2, 2, '2024-01-02', '2024-01-05'), (3, 1, '2024-01-10', '2024-01-10');50.0Customer 1's first order (delivery_id 1) is immediate. Customer 2's first order (delivery_id 2) is not immediate. 1 out of 2 = 50%.
CREATE TABLE delivery (delivery_id INT, customer_id INT, order_date TEXT, customer_pref_delivery_date TEXT); INSERT INTO delivery VALUES (1, 1, '2024-01-15', '2024-01-15'), (2, 2, '2024-01-16', '2024-01-16'), (3, 3, '2024-01-17', '2024-01-17'), (4, 1, '2024-01-20', '2024-01-22'), (5, 2, '2024-01-21', '2024-01-25');100.0All three customers have immediate first orders (order_date equals customer_pref_delivery_date). Customer 1's first order is delivery_id 1, customer 2's first order is delivery_id 2, and customer 3's first order is delivery_id 3. The later orders (delivery_id 4 and 5) are ignored since they're not first orders. Result: 3 immediate out of 3 first orders = 100%
CREATE TABLE delivery (delivery_id INT, customer_id INT, order_date TEXT, customer_pref_delivery_date TEXT); INSERT INTO delivery VALUES (10, 5, '2024-02-01', '2024-02-05'), (11, 6, '2024-02-02', '2024-02-10'), (12, 7, '2024-02-03', '2024-02-08'), (13, 8, '2024-02-04', '2024-02-15');0.0None of the four customers have immediate first orders. Customer 5 wants delivery on 2024-02-05 but ordered on 2024-02-01, customer 6 wants delivery on 2024-02-10 but ordered on 2024-02-02, customer 7 wants delivery on 2024-02-08 but ordered on 2024-02-03, and customer 8 wants delivery on 2024-02-15 but ordered on 2024-02-04. Result: 0 immediate out of 4 first orders = 0%
Constraints
- •
Consider only first order per customer