1174. Immediate Food Delivery II
On LeetCode ->Problem¶
Given a Delivery table, take each customer's earliest order only. Return the percentage of those first orders that were delivered immediately, rounded to 2 decimals.
Input:
# Delivery
------------+-------------+------------+-----------------------------
delivery_id | customer_id | order_date | customer_pref_delivery_date
------------+-------------+------------+-----------------------------
1 | 1 | 2020-01-01 | 2020-01-02
2 | 1 | 2020-01-05 | 2020-01-05
3 | 2 | 2020-01-03 | 2020-01-03
4 | 3 | 2020-01-02 | 2020-01-02
Output:
Key trick¶
Compute the first order per customer first, then average the boolean condition order_date = customer_pref_delivery_date.
Trap¶
- Do not average over all orders.
- Do not use
MIN(order_date)directly inWHERE. - When joining first orders back, join by both
customer_idandorder_date. - Round after multiplying by
100, not before.
Why is it interesting?¶
It tests the common interview pattern: filter to one row per group, then aggregate over that reduced set.
SQL solution¶
WITH first_orders AS (
-- One earliest order date per customer.
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM Delivery
GROUP BY customer_id
)
SELECT
-- AVG of 100/0 directly gives the percentage.
ROUND(
AVG(
CASE
WHEN d.order_date = d.customer_pref_delivery_date THEN 100.0
ELSE 0.0
END
),
2
) AS immediate_percentage
FROM Delivery AS d
JOIN first_orders AS f
ON d.customer_id = f.customer_id
AND d.order_date = f.first_order_date;
Pandas solution¶
import pandas as pd
def immediate_food_delivery(delivery: pd.DataFrame) -> pd.DataFrame:
# Guaranteed unique first order, so idxmin selects exactly one row per customer.
first_orders = delivery.loc[
delivery.groupby("customer_id")["order_date"].idxmin()
]
# Boolean mean gives the fraction of immediate first orders.
immediate_percentage = round(
100.0
* first_orders["order_date"]
.eq(first_orders["customer_pref_delivery_date"])
.mean(),
2,
)
return pd.DataFrame({"immediate_percentage": [immediate_percentage]})
Pytest test¶
import sqlite3
import pandas as pd
import pytest
SQL_QUERY = """
WITH first_orders AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM Delivery
GROUP BY customer_id
)
SELECT
ROUND(
AVG(
CASE
WHEN d.order_date = d.customer_pref_delivery_date THEN 100.0
ELSE 0.0
END
),
2
) AS immediate_percentage
FROM Delivery AS d
JOIN first_orders AS f
ON d.customer_id = f.customer_id
AND d.order_date = f.first_order_date;
"""
def pandas_solution(delivery: pd.DataFrame) -> pd.DataFrame:
first_orders = delivery.loc[
delivery.groupby("customer_id")["order_date"].idxmin()
]
immediate_percentage = round(
100.0
* first_orders["order_date"]
.eq(first_orders["customer_pref_delivery_date"])
.mean(),
2,
)
return pd.DataFrame({"immediate_percentage": [immediate_percentage]})
def run_sql(rows):
conn = sqlite3.connect(":memory:")
conn.execute(
"""
CREATE TABLE Delivery (
delivery_id INTEGER,
customer_id INTEGER,
order_date TEXT,
customer_pref_delivery_date TEXT
)
"""
)
conn.executemany(
"""
INSERT INTO Delivery (
delivery_id,
customer_id,
order_date,
customer_pref_delivery_date
)
VALUES (?, ?, ?, ?)
""",
rows,
)
result = conn.execute(SQL_QUERY).fetchone()[0]
conn.close()
return result
def make_dataframe(rows):
delivery = pd.DataFrame(
rows,
columns=[
"delivery_id",
"customer_id",
"order_date",
"customer_pref_delivery_date",
],
)
delivery["order_date"] = pd.to_datetime(delivery["order_date"])
delivery["customer_pref_delivery_date"] = pd.to_datetime(
delivery["customer_pref_delivery_date"]
)
return delivery
@pytest.mark.parametrize(
"rows, expected",
[
(
[
(1, 1, "2019-08-01", "2019-08-02"),
(2, 2, "2019-08-02", "2019-08-02"),
(3, 1, "2019-08-11", "2019-08-12"),
(4, 3, "2019-08-24", "2019-08-24"),
(5, 3, "2019-08-21", "2019-08-22"),
(6, 2, "2019-08-11", "2019-08-13"),
(7, 4, "2019-08-09", "2019-08-09"),
],
50.00,
),
(
[
(1, 1, "2020-01-01", "2020-01-01"),
(2, 2, "2020-01-02", "2020-01-02"),
(3, 3, "2020-01-03", "2020-01-03"),
],
100.00,
),
(
[
(1, 1, "2020-01-01", "2020-01-02"),
(2, 2, "2020-01-02", "2020-01-03"),
(3, 3, "2020-01-03", "2020-01-04"),
],
0.00,
),
(
[
(1, 1, "2020-01-01", "2020-01-02"),
(2, 2, "2020-01-02", "2020-01-02"),
(3, 3, "2019-12-31", "2020-01-01"),
(4, 3, "2020-01-01", "2020-01-01"),
],
33.33,
),
(
[
(1, 1, "2020-01-01", "2020-01-01"),
(2, 2, "2020-01-02", "2020-01-03"),
(3, 3, "2020-01-03", "2020-01-04"),
(4, 4, "2020-01-04", "2020-01-05"),
(5, 5, "2020-01-05", "2020-01-06"),
(6, 6, "2020-01-06", "2020-01-07"),
],
16.67,
),
],
)
def test_immediate_food_delivery_sql_and_pandas(rows, expected):
sql_result = run_sql(rows)
delivery = make_dataframe(rows)
pandas_result = pandas_solution(delivery).loc[0, "immediate_percentage"]
assert sql_result == pytest.approx(expected)
assert pandas_result == pytest.approx(expected)
Comment on my solution¶
- Your SQL solution is correct.
- The aggregate error is expected because
WHEREis evaluated before aggregation. - Your Pandas solution joins only on the date, so it can match orders from the wrong customer.
- Your Pandas rounding is also wrong because it rounds the fraction before multiplying by
100.
-- WORKS
WITH first_orders AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM Delivery
GROUP BY customer_id
)
SELECT
ROUND(100.0 * AVG(
CASE WHEN d.order_date = d.customer_pref_delivery_date THEN 1.0
ELSE 0.0
END
), 2) AS immediate_percentage
FROM Delivery AS d
JOIN first_orders AS f
ON f.customer_id = d.customer_id
AND f.first_order_date = d.order_date
-- Error: aggregate functions are not allowed in WHERE
SELECT
ROUND(AVG(
CASE WHEN order_date = customer_pref_delivery_date THEN 1.0
ELSE 0.0
END
), 2) AS immediate_percentage
FROM Delivery
WHERE order_date = MIN(order_date);
import pandas as pd
# Wrong Answer: 9/23 testcases passed
def immediate_food_delivery(delivery: pd.DataFrame) -> pd.DataFrame:
first_orders = (
delivery.groupby("customer_id", as_index=False)
.agg(first_order_date=("order_date", "min"))
)
merged = (
delivery.merge(
first_orders,
left_on="order_date",
right_on="first_order_date",
how="inner"
)
)
merged["immediate"] = (merged["order_date"].eq(merged["customer_pref_delivery_date"])).astype(float)
immediate_percentage = 100.0 * merged["immediate"].mean().round(2)
return pd.DataFrame({"immediate_percentage":[immediate_percentage]})
# WORKS
# Written after reading AI comments
def immediate_food_delivery(delivery: pd.DataFrame) -> pd.DataFrame:
first_orders = (
delivery.groupby("customer_id", as_index=False)
.agg(first_order_date=("order_date", "min"))
)
merged = (
delivery.merge(
first_orders,
left_on=["customer_id", "order_date"],
right_on=["customer_id", "first_order_date"],
how="inner"
)
)
merged["immediate"] = (merged["order_date"].eq(merged["customer_pref_delivery_date"])).astype(float)
immediate_percentage = (merged["immediate"].mean() * 100.0).round(2)
return pd.DataFrame({"immediate_percentage":[immediate_percentage]})