mediumWindow Functions Subqueries Aggregation

Immediate Food Delivery

Report the percentage of customers' first orders that were delivered immediately.

Problem statement

Prompt

Given the delivery table below (one row per delivery), write a query that reports the percentage of immediate first orders — orders that are a customer's first order (earliest order_date; ties broken by the smaller customer_pref_delivery_date) and were delivered on the preferred date.

An order is "immediate" when order_date = customer_pref_delivery_date.
Output a single column immediate_percentage rounded to 2 decimal places (ROUND(..., 2)).

Example

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2024-01-01	2024-01-01
2	1	2024-01-02	2024-01-03
3	2	2024-01-02	2024-01-02

First orders: customer 1 → delivery 1 (immediate), customer 2 → delivery 3 (immediate). Both immediate → 100.0.

Expected result:

immediate_percentage
100

Constraints

delivery.delivery_id is the primary key.
A customer's first order is the one with the smallest order_date; if tied, the smallest customer_pref_delivery_date.

Tips

Rank orders per customer with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, customer_pref_delivery_date), keep rn = 1, then average CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END.
ROUND(100.0 * SUM(is_immediate) / COUNT(*), 2) keeps the division in floating point.

Schema

CREATE TABLE delivery (
    delivery_id                INTEGER PRIMARY KEY,
    customer_id                INTEGER NOT NULL,
    order_date                 DATE NOT NULL,
    customer_pref_delivery_date DATE NOT NULL
);

Sample data

INSERT INTO delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) VALUES
  (1, 1, '2024-01-01', '2024-01-01'),
  (2, 1, '2024-01-02', '2024-01-03'),
  (3, 2, '2024-01-02', '2024-01-02'),
  (4, 2, '2024-01-03', '2024-01-05'),
  (5, 3, '2024-01-03', '2024-01-04'),
  (6, 3, '2024-01-04', '2024-01-04');

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: percentage of customers whose first order was immediate.
WITH ranked AS (
    SELECT customer_id, order_date, customer_pref_delivery_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date, customer_pref_delivery_date
           ) AS rn
    FROM delivery
),
first_orders AS (
    SELECT order_date, customer_pref_delivery_date
    FROM ranked
    WHERE rn = 1
)
SELECT ROUND(
           100.0 * SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END)
           / COUNT(*),
           2
       ) AS immediate_percentage
FROM first_orders;

Key concepts

ROW_NUMBER
Window functions
Percentage
julianday