3705. Find Golden Hour Customers

Tables

erDiagram
	restaurant_orders {
		order_id int PK
		customer_id int
		order_timestamp datetime
		order_amount decimal
		payment_method varchar "cash, card, app."
		order_rating int "1~5, null"
		}
Mermaid
복사

Question

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:0014:00 or 18:0021: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.

DataFlow

count order case when peak time rating > 4.0 and 50% at least their orders

Answer

WITH order_cnt AS (
	SELECT
		customer_id,
		COUNT(order_id) AS order_cnt
	FROM restaurant_orders
	GROUP BY customer_id
	HAVING COUNT(order_id) >= 3
),

order_peak AS (
	SELECT
		o.customer_id,
		c.order_cnt,
		o.peak_cnt,
		o.rating_cnt
	FROM (
		SELECT
			customer_id,
			SUM(
				CASE
					WHEN (TIME(order_timestamp) >= '11:00' AND TIME(order_timestamp) <= '14:00')
					OR (TIME(order_timestamp) >= '18:00' AND TIME(order_timestamp) <= '21:00') THEN 1 ELSE 0
				END) AS peak_cnt,
			COUNT(order_rating) AS rating_cnt
		FROM restaurant_orders
		GROUP BY customer_id
	) o
	INNER JOIN order_cnt c
		ON o.customer_id = c.customer_id AND (o.peak_cnt / c.order_cnt) >= 0.6 AND (o.rating_cnt / c.order_cnt) >= 0.5
),

gold_cus AS (
	SELECT
		o.customer_id,
		c.order_cnt AS total_orders,
		ROUND(c.peak_cnt / c.order_cnt * 100) AS peak_hour_percentage,
		o.rating_avg AS average_rating
	FROM (
		SELECT
			customer_id,
			ROUND(AVG(order_rating), 2) AS rating_avg
		FROM restaurant_orders
		GROUP BY customer_id
		HAVING AVG(order_rating) >= 4.0
	) o
	INNER JOIN order_peak c
		ON o.customer_id = c.customer_id
)

SELECT
	*

FROM gold_cus
ORDER BY average_rating DESC, customer_id DESC;
SQL
복사

작성 후 확인해 보니, group by를 더 효율적으로 할 수 있다. 하지만 성능이 향상 되지 않음. 다른 답안들을 확인해본 결과 CTE, subquery를 사용해서 풀이 하였으나. 성능의 차이는 크지 않아서. 가독성이 좋은 쪽을 선택했다.