3705. Find Golden Hour Customers Solution

The problem

Table: 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 is the unique identifier for this table.
payment_method can be cash, card, or app.
order_rating is between 1 and 5, where 5 is the best (NULL if not rated).
order_timestamp contains both date and time information.

Write a solution to find golden hour customers - customers who consistently order during peak hours and provide high satisfaction. A customer is a golden hour customer if they meet ALL the following criteria:

Made at least 3 orders.
At least 60% of their orders are during peak hours (11:00-14:00 or 18:00-21:00).
Their average rating for rated orders is at least 4.0, round it to 2 decimal places.
Have rated at least 50% of their orders.

Return the result table ordered by average_rating in descending order, then by customer_id in descending order.

The result format is in the following example.

Example:

Input:

restaurant_orders table:

+----------+-------------+---------------------+--------------+----------------+--------------+
| 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            |
+----------+-------------+---------------------+--------------+----------------+--------------+

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.

Approach overview

Problem Overview: You get a table of customers and their orders with timestamps. Find the customers who meet the golden hour criteria defined in the statement, which compare each customer's qualifying orders against their total activity. The problem is a per-customer aggregation task, so the solution is a grouping problem, not a row-by-row comparison problem.

Approach 1: Correlated Subquery Per Customer (O(n * m) time, O(1) extra space)

For each customer, run a subquery that scans the orders table again to count total orders and count orders that fall inside the golden hour window. Then compare the two counts against the threshold in the outer query. This is easy to write and matches the statement word for word. The cost is that the orders table is rescanned once per customer, so it becomes slow as the number of customers grows. Use it only to verify logic on small inputs.

Approach 2: Grouping and Statistics with Conditional Aggregation (O(n) time, O(k) space)

Scan the orders table once and group by customer_id. Inside the aggregation, count total orders with COUNT(*) and count qualifying orders with SUM(CASE WHEN ... THEN 1 ELSE 0 END) in MySQL, or with a boolean mask summed per group in Pandas. Compute the ratio or apply the threshold check in a HAVING clause (SQL) or a filter after groupby().agg() (Pandas). Here n is the number of order rows and k is the number of distinct customers, which is the size of the grouped result. This is the approach to use in production and in interviews. The database or DataFrame engine handles the single pass, and the filter logic lives in one place.

Watch the edge cases. Customers with zero orders may not appear in the orders table, so decide whether a LEFT JOIN from customers is needed. Watch integer division in MySQL when computing ratios, and use *1.0 or CAST to avoid truncation. In Pandas, make sure the boolean mask is built from the timestamp column after converting it with pd.to_datetime.

Recommended for interviews: Interviewers expect Approach 2. The correlated subquery shows you understand the problem, but the single-pass grouped solution shows you know how to reduce work to one scan and push the filter into aggregation. Mention the tradeoff out loud. Explain why a per-customer rescan is wasteful, then write the grouped version. Practice this pattern on SQL problems and database problems, since the same GROUP BY with conditional counts appears across many questions. The grouping step also relies on the same idea as hash table counting, where each key accumulates its own statistics in one pass.

Solution

Thinking

The predicate depends on each customer's order count, peak-hour share, rated-order ratio, and average rating; a manual loop is easy to get wrong. Grouping by customer_id produces those aggregates in one pass, after which we filter by the given thresholds and sort by average rating and customer id.

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.

MySQL

# 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 5 * SUM(
        TIME(order_timestamp) BETWEEN '11:00:00' AND '14:00:00'
        OR TIME(order_timestamp) BETWEEN '18:00:00' AND '21:00:00'
    ) >= 3 * COUNT(1)
    AND average_rating >= 4.0
    AND SUM(order_rating IS NOT NULL) / total_orders >= 0.5
ORDER BY average_rating DESC, customer_id DESC;

Pandas

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)
        & (5 * grouped["peak_hour_count"] >= 3 * grouped["total_orders"])
        & (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"]
    ]

Try this approach in the editor

Complexity analysis

Approach	Time	Space	When to Use
Correlated Subquery Per Customer	O(n * m)	O(1) extra	Small datasets or quick logic checks
Grouping and Statistics with Conditional Aggregation	O(n)	O(k)	General case, interviews, and production queries

Frequently asked questions

Find Golden Hour Customers Python solution?

In Pandas, convert the timestamp column with pd.to_datetime, build a boolean mask for golden hour orders, then call groupby on customer_id and aggregate with count for total orders and sum of the mask for qualifying orders. Filter the resulting DataFrame with the threshold from the statement and return the customer columns. This runs in O(n) time with a single grouping pass.

Is Find Golden Hour Customers easy or hard?

It is rated Medium with an acceptance rate of about 35.3%. The logic is not hard once you see that it is a grouping problem. The difficulty comes from reading the golden hour rules carefully, handling customers with no qualifying orders, and avoiding integer division or time zone mistakes when computing ratios.

How to solve Find Golden Hour Customers in O(n)?

Use one GROUP BY on customer_id. Inside the aggregation, compute the total order count with COUNT(*) and the golden hour count with SUM(CASE WHEN the timestamp falls in the window THEN 1 ELSE 0 END). Then filter groups using HAVING with the threshold from the statement. Each order row is read once, so the total work is O(n).

What is the best approach for Find Golden Hour Customers?

The best approach is grouping and statistics with conditional aggregation. Group the orders by customer_id, count total orders and qualifying golden hour orders in one pass, then apply the threshold filter with HAVING in MySQL or a filter after groupby in Pandas. This runs in O(n) time with O(k) space, where n is the number of order rows and k is the number of customers.

What data structure is used in Find Golden Hour Customers?

The solution relies on grouping, which the database or DataFrame engine implements with a hash-based aggregation over customer_id. In SQL, this is a GROUP BY with conditional expressions. In Pandas, it is groupby with boolean masks summed per group. You do not need to build a custom hash map by hand.

What is the time complexity of Find Golden Hour Customers?

The optimal grouped solution runs in O(n) time, where n is the number of order rows, because it makes a single pass over the orders table. The naive correlated subquery runs in O(n * m) time, where m is the number of customers, because it rescans orders for each customer.

Is Find Golden Hour Customers asked at Google, Amazon, or Meta?

This problem is tagged as General on FleetCode and does not have confirmed company tags here. It is a SQL and Pandas aggregation problem, so the grouping and conditional counting skills it tests are common in data-focused interviews at large tech companies. Check the company tab on the problem page for the latest reported frequency.

Find Golden Hour Customers solution and explanation