문제 13
📍난이도 : 2
DVD Rental Store 데이터베이스에는 어느 한 DVD 대여 프랜차이즈의 고객 정보, DVD 대여 정보, 제공 중인 영화의 정보 등이 들어있습니다.
대여 매출이 높았던 상위 5명의 배우를 추리고 특별 코너를 만들어 프로모
션에 활용하려고 합니다. 데이터베이스를 조회해 배우별 대여 매출 합계를 계산하고, 그 중 상위 5명의 정보를 출력하는 쿼리를 작성해주세요. 배우별 대여 매출 합계는 배우가 출연한 작품들의 매출 합계입니다.
쿼리 결과에는 아래 컬럼이 있어야 하며, 매출액 기준 내림차순으로 정렬되어 있어야 합니다.
- first_name: 배우의 이름
- last_name: 배우의 성
- total_revenue: 배우 출연작들의 총 매출
풀이
작년에 때를 놓치고 영영 안 풀게 된 데이터리안의 SQL 어드벤트 캘린더. 올해는 늦더라도 모두 풀고 3번 이상 제출한 문제만이라도 리뷰하려 한다.
13일자 문제는 쉬워보이지만 트릭이 꽤 있어서 올해 어드벤트 문제 중 처음으로 2번 오답을 냈다.
DVD 대여점을 다룬 테이블은 총 15개인데 작품 자체와 연관된 건 많지 않다(Sakila 데이터 원본). 반 이상이 스토어나 대여 고객과 관련된 테이블이다.
다만 작품의 총 매출은 비디오마다 정액제로 매겨진 대여 요금이 아니라 실 결제 금액을 잘 찾아와야 한다. 나처럼 대충 보고 film 테이블의 rental_rate(대여 요금)만 참조하는 실수를 하면 쓸데없는 혼선이 생길 수 있다.
결국 이 문제의 관건은
1) rental, inventory, payment 테이블을 잘 조인해서 payment의 amount(결제 금액)를 데리고 오기
2) 배우 중 동명 이인이 있을 수 있다는 점에 유의하기
라고 보인다.
쿼리
오답 1
select a.first_name, a.last_name, sum(c.rental_rate) total_revenue from actor a
left join film_actor b on b.actor_id = a.actor_id
left join film c on c.film_id = b.film_id
group by 1,2
order by 3 desc
limit 5결과:
이렇게 하면 당연히 sum(렌탈 요금)은 실 결제 금액에 비해 너무 적거니와, 배우 중 Susan Davis가 둘이라서 중복 집계되어 1위가 되면서 순서도 틀렸다고 뜬다.
영문을 모르다가 actor_id로 한 번 더 group by 해주면서 실제 대여횟수가 제일 많은 (753번) Gina Degeneres를 식별하긴 했지만 이후에 한 번 더 까먹고 그냥 제출함... 🫡
오답 2
with film_base as (
select
b.film_id,
sum(c.amount) total_revenue
from rental a
left join inventory b on b.inventory_id = a.inventory_id
left join payment c on c.rental_id = a.rental_id
group by 1
order by 2 desc
)
select
first_name,
last_name,
sum(total_revenue) total_revenue
from actor a
left join film_actor b on b.actor_id = a.actor_id
left join film_base c on b.film_id = c.film_id
group by 1,2
order by 3 desc
limit 5 결과:
어찌저찌 제대로 된 total revenue를 찾아냈으나, 여전히 Susan Davis가 첫 행에 뜬다. 앗 맞다 actor_id...
실제로 name으로 group by해서 actor_id 개수를 세면 Susan Davis만 두 명인 것을 확인 가능하다.
select name, count(actor_id) cnt
from (select actor_id, concat(first_name," ", last_name) name from actor) a
group by 1 order by 2 desc
정답
with rent as (
select
b.film_id,
sum(c.amount) total_revenue
from rental a
left join inventory b on b.inventory_id = a.inventory_id
left join payment c on c.rental_id = a.rental_id
group by 1
order by 2 desc
)
select
a.first_name,
a.last_name,
sum(total_revenue) total_revenue
from actor a
left join film_actor b on b.actor_id = a.actor_id
left join rent c on b.film_id = c.film_id
group by 1,2,b.actor_id
order by 3 desc
limit 5 결과: 
드디어 깔끔하게 정답에 도달했다. 덜렁대다 괜히 한 번 더 기회 날린 게 아쉽고 영화 문제라 재미있어서 기록해봤다. PK로 group by 해주는 등 나처럼 성질 급한 solver들이 잊기 쉬운 기초를 노리는 문제라고 할 수 있겠다.
