Immediate Food Delivery Rate on First Orders in SQL

Question

If customer preferred delivery date matches order date, the order is 'immediate'; otherwise 'scheduled'. Find the percentage of immediate orders among the first orders of all customers, rounded to 2 decimal places.

Accepted Answer

Optimal Solution:
WITH first_orders AS (
  SELECT 
    delivery_id,
    customer_id,
    order_date,
    customer_pref_delivery_date,
    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date ASC) AS rn
  FROM delivery
)
SELECT 
  ROUND(
    100.0 * COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) / COUNT(*),
    2
  ) AS immediate_percentage
FROM first_orders
WHERE rn = 1;

Technical Explanation:
We use `ROW_NUMBER()` partitioned by `customer_id` ordered by `order_date ASC` to identify each customer's first order (`rn = 1`), then calculate the percentage of immediate deliveries.

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2026-08-01	2026-08-02
2	2	2026-08-02	2026-08-02
3	1	2026-08-11	2026-08-11