3705. 寻找黄金时段客户

题目描述

表：restaurant_orders

+------------------+----------+
| Column Name      | Type     | 
+------------------+----------+
| order_id         | int      |
| customer_id      | int      |
| order_timestamp  | datetime |
| order_amount     | decimal  |
| payment_method   | varchar  |
| order_rating     | int      |
+------------------+----------+
order_id 是这张表的唯一主键。
payment_method 可以是 cash，card 或 app。
order_rating 在 1 到 5 之间，其中 5 是最佳（如果没有评分则是 NULL）。
order_timestamp 同时包含日期和时间信息。

编写一个解决方案来寻找 黄金时间客户 - 高峰时段持续订购且满意度高的客户。客户若满足以下所有条件，则被视为 黄金时段客户：

进行至少 3 笔订单。
他们有至少 60% 的订单在 高峰时间 中（11:00-14:00 或 18:00-21:00）。
他们的 平均评分 至少为 4.0，四舍五入到小数点后 2 位。
已评价至少 50% 的订单。

返回结果表按 average_rating 降序排序，然后按 customer_id 降序排序。

结果格式如下所示。

示例：

输入：

restaurant_orders 表：

+----------+-------------+---------------------+--------------+----------------+--------------+
| order_id | customer_id | order_timestamp     | order_amount | payment_method | order_rating |
+----------+-------------+---------------------+--------------+----------------+--------------+
| 1        | 101         | 2024-03-01 12:30:00 | 25.50        | card           | 5            |
| 2        | 101         | 2024-03-02 19:15:00 | 32.00        | app            | 4            |
| 3        | 101         | 2024-03-03 13:45:00 | 28.75        | card           | 5            |
| 4        | 101         | 2024-03-04 20:30:00 | 41.00        | app            | NULL         |
| 5        | 102         | 2024-03-01 11:30:00 | 18.50        | cash           | 4            |
| 6        | 102         | 2024-03-02 12:00:00 | 22.00        | card           | 3            |
| 7        | 102         | 2024-03-03 15:30:00 | 19.75        | cash           | NULL         |
| 8        | 103         | 2024-03-01 19:00:00 | 55.00        | app            | 5            |
| 9        | 103         | 2024-03-02 20:45:00 | 48.50        | app            | 4            |
| 10       | 103         | 2024-03-03 18:30:00 | 62.00        | card           | 5            |
| 11       | 104         | 2024-03-01 10:00:00 | 15.00        | cash           | 3            |
| 12       | 104         | 2024-03-02 09:30:00 | 18.00        | cash           | 2            |
| 13       | 104         | 2024-03-03 16:00:00 | 20.00        | card           | 3            |
| 14       | 105         | 2024-03-01 12:15:00 | 30.00        | app            | 4            |
| 15       | 105         | 2024-03-02 13:00:00 | 35.50        | app            | 5            |
| 16       | 105         | 2024-03-03 11:45:00 | 28.00        | card           | 4            |
+----------+-------------+---------------------+--------------+----------------+--------------+

输出：

+-------------+--------------+----------------------+----------------+
| customer_id | total_orders | peak_hour_percentage | average_rating |
+-------------+--------------+----------------------+----------------+
| 103         | 3            | 100                  | 4.67           |
| 101         | 4            | 100                  | 4.67           |
| 105         | 3            | 100                  | 4.33           |
+-------------+--------------+----------------------+----------------+

解释：

客户 101：
- 总订单数：4（至少 3 笔）
- 高峰时间订单：4 笔中有 4 笔（12:30，19:15，13:45 和 20:30 在高峰时间）
- 高峰时间占比：100%（至少 60%）
- 已评分的订单：4 笔中有 3 笔（75% 评分完成率）
- 平均评分：(5+4+5)/3 = 4.67（至少 4.0）
- 结果：黄金时段客户
客户 102:
- 总订单数：3（至少 3 笔）
- 高峰时间订单：3 笔中有 2 笔（11:30，12:00 都在高峰时间，但 15:30 不是）
- 高峰时间占比：2/3 = 66.67%（至少 60%）
- 已评分的订单：3 笔中有 2 笔（66.67% 评分完成率）
- 平均评分：(4+3)/2 = 3.5（少于 4.0）
- 结果：不是黄金时段客户（平均评分太低）
客户 103:
- 总订单数：3（至少 3 笔）
- 高峰时间订单：3 笔中有 3 （19:00，20:45，18:30 都在傍晚高峰时间）
- 高峰时间占比：3/3 = 100%（至少 60%）
- 已评分的订单：3 笔中有 3 笔（100% 评分完成率）
- 平均评分：(5+4+5)/3 = 4.67（至少 4.0）
- 结果：黄金时段客户
客户 104:
- 总订单数：3（至少 3 笔）
- 高峰时间订单：3 笔中有 0 笔（10:00，09:30，16:00 都不在高峰时间）
- 高峰时间占比：0/3 = 0%（至少 60%）
- 结果：不是黄金时段客户（高峰时段订单不足）
客户 105:
- 总订单数：3（至少 3 笔）
- 高峰时间订单：3 笔中有 3 笔（12:15，13:00，11:45 都在中午高峰时间）
- 高峰时间占比：3/3 = 100%（至少 60%）
- 已评分的订单：3 笔中有 3 笔（100% 评分完成率）
- 平均评分：(4+5+4)/3 = 4.33（至少 4.0）
- 结果：黄金时段客户

结果表按 average_rating 降序排序，然后按 customer_id 降序排序。

解法

方法一：分组统计

我们可以将订单按照 customer_id 进行分组，统计每个顾客的总订单数、峰值时段订单数、评分订单数和平均评分，然后根据题目中的条件进行筛选，最后按照平均评分降序、顾客 ID 降序排序。

MySQLPython3

# Write your MySQL query statement below
SELECT
    customer_id,
    COUNT(1) total_orders,
    ROUND(
        SUM(
            TIME(order_timestamp) BETWEEN '11:00:00' AND '14:00:00'
            OR TIME(order_timestamp) BETWEEN '18:00:00' AND '21:00:00'
        ) / COUNT(1) * 100
    ) peak_hour_percentage,
    ROUND(AVG(order_rating), 2) average_rating
FROM restaurant_orders
GROUP BY customer_id
HAVING
    total_orders >= 3
    AND peak_hour_percentage >= 60
    AND average_rating >= 4.0
    AND SUM(order_rating IS NOT NULL) / total_orders >= 0.5
ORDER BY average_rating DESC, customer_id DESC;

import pandas as pd
import numpy as np


def find_golden_hour_customers(restaurant_orders: pd.DataFrame) -> pd.DataFrame:
    df = restaurant_orders.copy()
    df["order_timestamp"] = pd.to_datetime(df["order_timestamp"])
    df["is_peak_hour"] = df["order_timestamp"].dt.time.between(
        pd.to_datetime("11:00:00").time(), pd.to_datetime("14:00:00").time()
    ) | df["order_timestamp"].dt.time.between(
        pd.to_datetime("18:00:00").time(), pd.to_datetime("21:00:00").time()
    )
    grouped = (
        df.groupby("customer_id")
        .agg(
            total_orders=("order_timestamp", "count"),
            peak_hour_count=("is_peak_hour", "sum"),
            average_rating=("order_rating", lambda x: x.dropna().mean()),
            non_null_rating_count=("order_rating", lambda x: x.notna().sum()),
        )
        .reset_index()
    )
    grouped["average_rating"] = grouped["average_rating"].round(2)
    grouped["peak_hour_percentage"] = (
        grouped["peak_hour_count"] / grouped["total_orders"] * 100
    ).round()
    filtered = grouped[
        (grouped["total_orders"] >= 3)
        & (grouped["peak_hour_percentage"] >= 60)
        & (grouped["average_rating"] >= 4.0)
        & (grouped["non_null_rating_count"] / grouped["total_orders"] >= 0.5)
    ]
    filtered = filtered.sort_values(
        by=["average_rating", "customer_id"], ascending=[False, False]
    )
    return filtered[
        ["customer_id", "total_orders", "peak_hour_percentage", "average_rating"]
    ]

3705. 寻找黄金时段客户

题目描述

解法

方法一：分组统计

评论