Welcome to Subscribe On Youtube

3705. Find Golden Hour Customers

Description

Table: restaurant_orders

++-+
\| order_id         \| int      \|
\| customer_id      \| int      \|
\| order_timestamp  \| datetime \|
\| order_amount     \| decimal  \|
\| payment_method   \| varchar  \|
\| order_rating     \| int      \|
++-+-+-+--+
\| order_id \| customer_id \| order_timestamp     \| order_amount \| payment_method \| order_rating \|
++--+-+-+-+--+

Output:

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

Explanation:

  • Customer 101:
    • Total orders: 4 (at least 3) 
    • Peak hour orders: 4 out of 4 (12:30, 19:15, 13:45, and 20:30 are in peak hours)
    • Peak hour percentage: 100% (at least 60%) 
    • Rated orders: 3 out of 4 (75% rating completion) 
    • Average rating: (5+4+5)/3 = 4.67 (at least 4.0) 
    • Result: Golden hour customer
  • Customer 102:
    • Total orders: 3 (at least 3) 
    • Peak hour orders: 2 out of 3 (11:30, 12:00 are in peak hours; 15:30 is not)
    • Peak hour percentage: 2/3 = 66.67% (at least 60%) 
    • Rated orders: 2 out of 3 (66.67% rating completion) 
    • Average rating: (4+3)/2 = 3.5 (less than 4.0) 
    • Result: Not a golden hour customer (average rating too low)
  • Customer 103:
    • Total orders: 3 (at least 3) 
    • Peak hour orders: 3 out of 3 (19:00, 20:45, 18:30 all in evening peak)
    • Peak hour percentage: 3/3 = 100% (at least 60%) 
    • Rated orders: 3 out of 3 (100% rating completion) 
    • Average rating: (5+4+5)/3 = 4.67 (at least 4.0) 
    • Result: Golden hour customer
  • Customer 104:
    • Total orders: 3 (at least 3) 
    • Peak hour orders: 0 out of 3 (10:00, 09:30, 16:00 all outside peak hours)
    • Peak hour percentage: 0/3 = 0% (less than 60%) 
    • Result: Not a golden hour customer (insufficient peak hour orders)
  • Customer 105:
    • Total orders: 3 (at least 3) 
    • Peak hour orders: 3 out of 3 (12:15, 13:00, 11:45 all in lunch peak)
    • Peak hour percentage: 3/3 = 100% (at least 60%) 
    • Rated orders: 3 out of 3 (100% rating completion) 
    • Average rating: (4+5+4)/3 = 4.33 (at least 4.0) 
    • Result: Golden hour customer

The results table is ordered by average_rating DESC, then customer_id DESC.

</div>

Solutions

Solution 1: Grouping and Statistics

We can group the orders by customer_id and calculate the total number of orders, number of orders during peak hours, number of rated orders, and average rating for each customer. Then we filter based on the conditions in the problem and sort by average rating in descending order, followed by customer ID in descending order.

  • 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"]
    ]


  • # 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;

All Problems

All Solutions