[SQL] 레스토랑의 요일별 VIP

문제

https://solvesql.com/problems/restaurant-vip/

https://solvesql.com/problems/restaurant-vip/

 

---

아이디어

- 서브쿼리 풀이

- 파티션 풀이 

코드 

- 서브 쿼리 풀이 

-- 요일별로 가장 높은 금액의 결제 내역 출력 
SELECT t.total_bill, t.tip, t.sex, t.smoker, t.day, t.time, t.size 
FROM tips t
JOIN (
  SELECT day, max(total_bill) as max_total_bil
  FROM tips 
  GROUP BY day
) as m 
ON t.day = m.day AND t.total_bill = m.max_total_bil;

 

- RANK() OVER (PARTITION BY .... ) 

SELECT r.total_bill, r.tip, r.sex, r.smoker, r.day, r.time, r.size
FROM (
  SELECT *, 
  RANK() OVER (PARTITION BY day ORDER BY total_bill DESC) as rnk
  FROM tips
) as r
WHERE rnk = 1;