[TIL175] leetcode - Find Golden Hour Customers

문제 설명

음식 주문 데이터를 이용하여 피크 시간에 꾸준히 주문하면서 만족도도 높은 고객(Golden Hour Customer) 을 찾는 문제였다.

Golden Hour Customer가 되기 위해서는 다음 네 가지 조건을 모두 만족해야 한다.

• 주문 횟수가 3건 이상
• 전체 주문 중 60% 이상이 피크 시간(11:00~14:00, 18:00~21:00)에 발생
• 평점을 남긴 주문의 평균 평점이 4.0 이상
• 전체 주문 중 50% 이상이 평점을 남긴 주문

출력해야 하는 컬럼은 다음과 같다.

• customer_id
• total_orders
• peak_hour_percentage
• average_rating

이 문제는 조건부 집계(Conditional Aggregation) 를 이용하여 여러 조건을 동시에 계산하는 집계 문제였다.

---

내가 처음 생각한 접근

처음에는 고객별로 주문을 그룹화한 뒤,

• 전체 주문 수
• 피크 시간 주문 수
• 평균 평점

을 계산하면 된다고 생각했다.

그래서

GROUP BY customer_id

를 먼저 떠올렸고,

피크 시간 주문은

SUM(
    CASE WHEN ...
    THEN 1
    ELSE 0
END)

으로 계산했다.

접근 자체는 맞았다.

하지만 시간 비교를 어떻게 해야 하는지를 잘못 생각한 것이 오답의 원인이었다.

---

내가 헷갈렸던 부분

1. HOUR()를 사용하면 시간이 아니라 "시(Hour)"만 비교한다.

처음에는

HOUR(order_timestamp)

를 사용했다.

왜냐하면 문제에서

11~14시

18~21시

라고 적혀 있어서

시간(hour)만 비교하면 된다고 생각했기 때문이다.

 

예를 들어

14:35

는

HOUR(order_timestamp)

를 사용하면

14

가 된다.

그러면

BETWEEN 11 AND 14

조건을 만족하게 된다.

즉

14:01

14:20

14:59

모두 피크 시간으로 계산된다.

 

하지만 문제에서는

11:00 ~ 14:00

라고 되어 있었다.

 

즉

14:01

부터는 피크 시간이 아니다.

 

그래서

TIME_FORMAT(order_timestamp,'%H:%i')

를 사용하여

14:30

↓

14:30

처럼 시와 분까지 비교해야 했다.

이번 문제를 통해 시간(hour)을 비교하는 문제와 시:분(time)을 비교하는 문제는 완전히 다르다는 것을 배웠다.

---

2. 문제의 시간을 내가 해석하지 말고 그대로 SQL로 옮겨야 한다.

처음에는

HOUR(order_timestamp)
BETWEEN 11 AND 14

를 작성했다.

하지만 문제에서는

11:00

~

14:00

라고 정확하게 적혀 있었다.

결국 내가 문제를 단순화해서 해석한 것이었다.

앞으로는

문제에서

09:30

~

17:45

처럼 분(minute)까지 제시하면

반드시

TIME()

TIME_FORMAT()

을 먼저 떠올려야 한다.

---

3. 평균 평점 조건에서는 ROUND를 먼저 하면 안 된다.

처음에는

ROUND(AVG(order_rating),2) >=4

를 사용했다.

하지만 평균을 반올림한 뒤 비교하면

실제 평균이

3.996

이어도

4.00

으로 반올림되어 조건을 만족하게 된다.

문제에서는

평균이 4 이상이어야 하는 것이므로

AVG(order_rating) >=4

로 먼저 비교하고,

출력할 때만

ROUND(AVG(order_rating),2)

를 사용하는 것이 맞다.

---

내가 작성한 쿼리

-- golden hour customers : 피크시간에 꾸준히 구매하고 높은 만족도를 보임.
-- - 주문건수 3개 이상, 피크시간(11-14, 18-21) 구매 60% 이상, 평균 만족도 4점이상 ROUND(, 2)
-- - 50%이상 만족도 남김
-- order by average_rating desc, customer_id desc
select distinct customer_id,
count(*) as total_orders,
-- hour() : 해당 숫자가 들어간 컬럼은 모두 반환 (시간이 넘어가도)
-- time_format(order_timestamp, '%H:%i')
ROUND(
    sum(Case when ( time_format(order_timestamp, '%H:%i') between '11:00' AND '14:00') OR ( time_format(order_timestamp, '%H:%i') between '18:00' AND '21:00') THEN 1 ELSE 0 END )*100 / count(*) )  peak_hour_percentage,
ROUND(avg(order_rating),2) as average_rating
from restaurant_orders
group by customer_id 
having count(*) >=3 
AND sum(Case when ( time_format(order_timestamp, '%H:%i') between '11:00' AND '14:00') OR ( time_format(order_timestamp, '%H:%i') between '18:00' AND '21:00')  THEN 1 ELSE 0 END ) / count(order_id) >= 0.6 
AND sum(case when order_rating IS NOT NULL THEN 1 ELSE 0 END)*100 / count(*) >=50
AND avg(order_rating) >= 4
order by average_rating desc, customer_id desc

---

핵심 SQL 개념 정리

TIME_FORMAT()

왜 사용하는가?

시간(hour)이 아니라

시:분 단위까지 정확하게 비교하기 위해 사용한다.

이번 문제에서는

11:00~14:00

18:00~21:00

를 정확하게 비교하기 위해 사용하였다.

실무 활용

• 점심시간 주문 분석
• 출퇴근 시간 분석
• 이벤트 시간 분석

시간 범위가

09:30

14:45

처럼 분(minute)까지 주어지면

HOUR()보다 TIME() 또는 TIME_FORMAT()이 적절하다.

---

함수 비교 정리

HOUR() vs TIME_FORMAT()

내가 처음 사용한 함수

HOUR(order_timestamp)

정답에서 사용한 함수

TIME_FORMAT(order_timestamp,'%H:%i')

차이점

HOUR()는 시(hour)만 비교하므로 14:59도 14시로 인식한다.

vs 

TIME_FORMAT()은 시와 분을 모두 비교하므로 14:00까지만 포함되고 14:01부터는 제외된다.

 

문제 조건 사용하는 함수

오전 9시~11시	HOUR() 가능
09:30~11:15	TIME() 또는 TIME_FORMAT()
날짜만 비교	DATE()
월 비교	MONTH()
요일 비교	DAYOFWEEK()

---

오늘 배운 점

시간 관련 문제에서는 시간(hour)을 비교하는 것인지, 시:분(time)을 비교하는 것인지 먼저 확인해야 한다.

또한 조건 비교에서는 ROUND를 사용하지 말고, 실제 계산값으로 비교한 뒤 출력 형식만 ROUND로 맞추는 것이 안전하다.

---

다음에 같은 문제를 만나면

먼저

문제가

시간(hour)을 비교하는가?

↓

시:분(time)을 비교하는가?

를 확인한 뒤,

조건부 집계를 이용하여 비율을 계산한다.

---

핵심 한 줄 요약

시간 범위가 시:분 단위로 주어진 문제는 TIME_FORMAT()으로 정확하게 비교하고, 조건부 집계(SUM(CASE WHEN))를 이용해 비율을 계산한 뒤 HAVING에서 필터링하는 패턴으로 해결한다.