1174. Immediate Food Delivery II

SQL 50 · Basic Aggregate Functions (day 3). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One number: share of first orders that were immediate.

2. Approach

Keep first orders with (customer_id, order_date) IN (SELECT customer_id, MIN(order_date) …), then average “order date = preferred date” × 100.

3. The trap

Compute “first order” per customer before averaging.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2)
  AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
  SELECT customer_id, MIN(order_date)
  FROM Delivery
  GROUP BY customer_id
);SELECT ROUND(AVG((order_date = customer_pref_delivery_date)::int) * 100, 2)
  AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
  SELECT customer_id, MIN(order_date)
  FROM Delivery
  GROUP BY customer_id
);

Patterns used

Toolbox · Phrase finder