SQL 코딩테스트 문제 풀이(3) - 레스토랑 요일 별 구매금액 Top 3 영수증(solvesql, RANK(), DENSE_RANK(), UNION, JOIN, 윈도우 함수)

 

📝목차

1. SQL 코딩테스트 문제 소개 - 레스토랑 요일 별 구매금액 Top 3 영수증(solvesql)
2. 풀이 1 - 요일별 추출 후 UNION
3. 풀이 2 - DENSE_RANK(), CHAT-GPT 활용
4. SQL 문법 설명 - RANK와 DENSE_RANK()

 

정답 바로가기

 

SQL 코딩테스트 문제 풀이 세 번째 시간이다.

1. SQL 코딩테스트 문제 소개 - 레스토랑 요일 별 구매금액 Top 3 영수증(solvesql)

이번 문제는 solvesql 사이트> 데이터리안 SQL 캠프 실전반> 2페이지에서 접할 수 있다.

난이도는 4로 조금 어려운 정도인데, 사실 DENSE_RANK()에 대해 알고 있다면 그렇게 어렵지 않을 수 있다.

하지만 나는  DENSE_RANK()를 몰랐기 때문에 기본 문법을 통해서 해결했다.

 

문제 바로가기

https://solvesql.com/problems/top-3-bill/

solvesql 사이트는 해설을 따로 제공하지 않기 때문에 본 글을 통해 도움을 얻길 바란다!

https://solvesql.com/problems/top-3-bill/

해당 문제는 요일별로 금액에 따라 순위를 계산하고, 3위까지의 영수증만 모아서 출력하는 것으로 윈도우 함수를 활용하는 유형이다. 하지만 기본적인 JOIN과 UNION을 사용해도 해결이 가능하다.(쿼리가 조금 길어지기는 하지만 ㅎㅎ)

 

나는 3단계로 문제를 해결했다.

  1. Sub  Query를 통해 요일별로 TOP 3 금액 구하기
  2. JOIN 사용하여 TOP 3 금액과 일치하는 영수증 추출
  3. UNION을 사용하여 모든 요일의 결과를 수직 결합

 

주의점

상위 3명이 아니라 상위 3개의 금액을 가진 모든 영수증을 출력해야한다.

예를 들어 금액이 100, 90, 90, 80이라면 4개의 영수증을 모두 출력해야 한다.

 

2. 풀이 1 - 요일별 추출 후 UNION

1) Sub Query를 통해 요일별로 TOP 3 금액 구하 

select distinct total_bill
from tips
where day = 'Sun'
order by 1 desc
limit 3

 

SQL 쿼리 실행 결과

실행 결과

 

 

Tips 테이블에서 일요일에 해당하는 데이터만 필터링한 뒤 total_bill(식사 금액) 열만 출력한다. SELECT DISTINCT total_bill을 통해서 중복을 제거함으로써 공동 순위가 발생하는 문제를 해결할 수 있다. 그다음 ORDER BY 1 DESC LIMIT 3을 통해서 1번째 열(즉 total_bill) 기준으로 내림차순 정렬을 한 뒤 상위 3개의 데이터만 출력한다.

 

해당 쿼리를 Sub Query로 사용해서 이후 상위 3개의 금액에 대한 데이터만 추출할 것이다.

2) JOIN 사용하여 TOP 3 금액과 일치하는 영수증 추출

select day, time, sex, a.total_bill
from tips a
join (select distinct total_bill
  from tips
  where day = 'Sun'
  order by 1 desc
  limit 3) b
on a.total_bill = b.total_bill
where day = 'Sun'

 

SQL 쿼리 실행 결과

실행 결과

 

원래의 Tips 테이블과 1번에서 얻은 테이블(상위 3개의 금액)을 금액(total_bill) 기준으로 INNER JOIN 한다. INNER JOIN을 하면 양쪽 테이블이 모두 공통으로 가지고 있는 값(교집합)만 가져올 수 있다. 따라서 상위 3개의 금액에 해당하는 영수증 데이터만 추출할 수 있다.

JOIN한 뒤에 Where day = 'Sun'으로 한번 더 필터링을 해주지 않으면, 원 테이블이 가지고 있던 '동일한 금액이지만 다른 요일의 데이터'가 함께 추출될 수 있다. 따라서 꼭 다시 필터링을 해야 한다.

 

3) UNION을 사용하여 모든 요일의 결과를 수직 결합

select day, time, sex, a.total_bill
from tips a
join (select distinct total_bill
  from tips
  where day = 'Sun'
  order by 1 desc
  limit 3) b
on a.total_bill = b.total_bill
where day = 'Sun'

union

select day, time, sex, a.total_bill
from tips a
join (select distinct total_bill
  from tips
  where day = 'Sat'
  order by 1 desc
  limit 3) b
on a.total_bill = b.total_bill
where day = 'Sat'

UNION

select day, time, sex, a.total_bill
from tips a
join (select distinct total_bill
  from tips
  where day = 'Fri'
  order by 1 desc
  limit 3) b
on a.total_bill = b.total_bill
where day = 'Fri'

UNION

select day, time, sex, a.total_bill
from tips a
join (select distinct total_bill
  from tips
  where day = 'Thur'
  order by 1 desc
  limit 3) b
on a.total_bill = b.total_bill
where day = 'Thur'

 

SQL 쿼리 실행 결과

실행 결과

 

쿼리가 갑자기 너무 길어져서 복잡해 보일 수 있다. 하지만 요일만 바꾼 뒤 같은 코드를 복사 붙여 넣기 하여 UNION으로 수직 결합한 것 밖에 없다.

목, 금, 토, 일 4개의 요일만 작성한 이유는 쿼리를 시작하기 앞서 어떤 요일이 있는지 확인하였기 때문이다.

SELECT DISTINCT day
FROM tips

이 쿼리를 실행하면 Unique 한 요일만 확인할 수 있다.

문제점이 많다.

 

그런데 쿼리가 너무 길지 않은가? 직관적일 수는 있지만 가독성이 떨어진다.
성능면에서도 좋지 않다. tips 테이블도 요일 개수만큼(4번) 스캔하여야 하고, 매번 정렬하여 상위 3개를 뽑아야 한다.

유지 보수성도 좋지 않다. 만약 가게가 다른 요일 장사를 한다면 새로운 요일을 추가해야 돼서 쿼리를 수정해야 한다.

윈도우 함수 DENSE_RANK()를 사용하면 이 문제를 해결할 수 있다.

 

3. 풀이 2 - DENSE_RANK(), CHAT-GPT 활용

CHAT-GPT는 윈도우 함수 중 하나인 DENSE_RANK를 사용하여 문제를 깔끔하게 해결했다.

모범 답안

SELECT day, time, sex, total_bill
FROM (
    SELECT day, time, sex, total_bill,
           DENSE_RANK() OVER (PARTITION BY day ORDER BY total_bill DESC) AS rank
    FROM tips
) ranked
WHERE rank <= 3;

윈도우 함수란?

- 윈도우 함수는 쿼리 결과의 각 행(row)에 대해 파티션(그룹) 별로 집계값이나 순위 등을 계산해 주는 함수이다
- OVER (PARTITION BY [A] ORDER BY [B]) 구문을 통해 A를 기준으로 그룹화하고, B로 정렬한다.

 

FROM 절의 Sub Query를 실행시키면 순위가 적힌 새로운 행을 만들 수 있다.

수요일은 수요일끼리, 목요일은 목요일끼리 순위가 매겨진다.

순서대로 풀어서 설명을 하자면,

PARTITION BY day	요일에 따라 그룹으로 분류한 뒤
ORDER BY total_bill DESC	그룹 내에서 total_bill에 따라 내림차순 정렬하여
DENSE_RANK()	순위를 매겨라

 

Sub Query 실행 결과

실행 결과

 

순위를 매긴 다음 WHERE rank <= 3을 통해 TOP 3 순위만 필터링을 한 뒤 출력하면 정답이다.

이 방식은 기존 내가 푼 방식에 비해 테이블 스캔을 적게 하기 때문에 성능이 좋고, 깔끔해서 가독성이 좋다.

그리고 요일이 추가되어도 쿼리를 수정할 필요가 없기 때문에 유지보수성이 높다.

 

여기서 한 가지 의문이 드는데, RANK()와 DENSE_RANK()는 뭐가 다를까?

4. SQL 문법 설명 - RANK와 DENSE_RANK()

해당 문제에서는 DENSE_RANK()를 사용하는데, 두 문법을 비교해 보자.

RANK()와 DENSE_RANK() 모두 순위를 매기는 윈도우 함수이다.

하지만 공동 순위를 처리하는 방식이 다르다. RANK()는 동점 개수만큼 건너뛰어 순위를 부여하고, DENSE_RANK()는 순위를 건너뛰지 않고 연속적으로 부여한다.

 

<100, 90, 90, 80이 있을 때 방식 비교>

total_bill	RANK() 사용 시	DENSE_RANK() 사용 시
100	1	1
90	2	2
90	2	2
80	4	3

 

마무리

오늘은 세 번째 SQL 코딩테스트 문제 풀이를 해보았다. solvesql의 '레스토랑 요일 별 구매금액 Top 3 영수증' 문제였으며 RANK(), DENSE_RANK(), UNION, JOIN, 윈도우 함수에 대해 더 자세히 공부하는 계기가 되었다. 다음엔 어떤 문제를 풀어볼까? 두근두근~