Share of Customers Whose First Grocery Order Came Same Day — SQL Medium Problem

Problem statement

A grocery app lets customers pick a delivery date when they order; an order is same-day when deliver_on equals placed_on. A customer's first order is the one with the earliest placed_on — no customer places two orders on the same day, so it is unique.

Return one row with one column, same_day_pct: the percentage of customers whose first order was same-day, rounded to 2 decimal places. Every customer with at least one order counts once. If there are no orders at all, the percentage is NULL.

Tables

Table: GroceryOrder

Primary key: order_id.

deliver_on is the delivery date the customer chose, never earlier than placed_on. order_id values are not in date order.

Examples

Example 1

GroceryOrder

Output

How to solve Share of Customers Whose First Grocery Order Came Same Day

Two steps: find each customer's first order, then measure what share of those first orders were same-day.

The first order. The earliest date per customer is MIN(placed_on) grouped by customer. To get the whole order back, keep the orders whose (customer_id, placed_on) pair is among those minimums — a row-value IN — or join the orders to the grouped minimums on both columns. Because a customer never orders twice on one day, exactly one row per customer survives. Note that order_id is not a safe shortcut: a smaller id may have been placed later.

The percentage. Over the surviving rows, SUM(placed_on = deliver_on) counts the same-day first orders (the comparison is 1 or 0), and dividing by COUNT(*) — one row per customer — and multiplying by 100 gives the percentage, which ROUND(…, 2) rounds. AVG of the same 1/0 comparison is that ratio in one function. With no orders at all, both forms give NULL.

A frequent wrong answer computes the share over all orders instead of first orders; another counts a customer as same-day if any of their orders was.

Alternatives: number each customer's orders with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_on) and keep row 1, or keep the orders for which NOT EXISTS an earlier order by the same customer. All of them group or sort per customer, so the cost is about O(n log n) with an index on (customer_id, placed_on).

Reference solution (MySQL)

SELECT ROUND(100 * SUM(placed_on = deliver_on) / COUNT(*), 2) AS same_day_pct
FROM GroceryOrder
WHERE (customer_id, placed_on) IN (
  SELECT customer_id, MIN(placed_on) FROM GroceryOrder GROUP BY customer_id
)

Another way

SELECT ROUND(AVG(placed_on = deliver_on) * 100, 2) AS same_day_pct
FROM (
  SELECT placed_on, deliver_on, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_on) AS rn
  FROM GroceryOrder
) ranked
WHERE rn = 1

Another way

SELECT ROUND(SUM(CASE WHEN o.deliver_on = o.placed_on THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS same_day_pct
FROM GroceryOrder o
JOIN (SELECT customer_id, MIN(placed_on) AS first_day FROM GroceryOrder GROUP BY customer_id) f
  ON f.customer_id = o.customer_id AND f.first_day = o.placed_on

Another way

SELECT ROUND(100 * AVG(CASE WHEN o.deliver_on = o.placed_on THEN 1 ELSE 0 END), 2) AS same_day_pct
FROM GroceryOrder o
WHERE NOT EXISTS (SELECT 1 FROM GroceryOrder e WHERE e.customer_id = o.customer_id AND e.placed_on < o.placed_on)