문제 14
📍난이도 : 3
Amazon Top 50 Bestselling Books 데이터베이스에는 아마존 쇼핑몰에서 2009년부터 2019년까지 매년 집계한 판매량 상위 50위의 책 정보를 담고 있습니다.
북클럽을 운영하는 당신은 화제성과 작품성을 모두 잡은 소설 작가를 찾아 이달의 작가로 선정하고 해당 작가의 작품들을 리뷰하는 시간을 가져보려고 합니다. 후보 작가 선정을 위해 당신은 아래와 같은 기준을 선정했습니다.
데이터베이스를 조회해 기준에 맞는 후보 작가 이름을 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 작가 이름이 출력되는 컬럼 하나만 있어야 하고, 작가 이름 기준 사전순으로 정렬되어 있어야 합니다.
- 아마존 판매량 상위 50위 이내에 작품, 연도 상관 없이 2회 이상 등재
- 해당 작가 작품들의 평균 사용자 평점이 4.5점 이상
- 해당 작가 작품들의 평균 리뷰 수가 소설 분야 작품들의 평균 리뷰 수 이상
풀이
아마존 판매량 상위 50위 이내에 작품, 연도 상관 없이 2회 이상 등재를 ' ① 50위 이내의 작품, ② 연도 상관 없이 2회 이상 등재'로 혼자 잘못 해석해서 괜히 돌아간 문제다. 난이도는 3이지만 결국 조건절로 이것저것 처리되니 어렵지 않다.
- 작가의 모든 작품 중 베스트셀러에 오른 기록을 찾고
- 그 작가가 쓴 모든 작품의 평균 사용자 평점, 평균 리뷰수에
- 전체 픽션 장르 평균 리뷰수를 붙여서 필터링
이렇게만 해주면 된다.
쿼리
오답 1
with base as (
select
genre,
author,
count(distinct name) book_cnt,
avg(user_rating) user_rating,
sum(reviews) reviews,
count(year) best_years
from books
where genre = 'Fiction'
group by 1, 2
order by 4 desc
)
select
author
from base
left join (select avg(reviews) all_reviews from books where genre = 'Fiction') total on 1=1
where
best_years >= 2
and user_rating >= 4.5
and reviews >= all_reviews
order by 1이렇게 base CTE에서 genre = 'Fiction' 필터를 걸어버리면 작가의 픽션 작품 중 베스트셀러가 된 것만 집계해버리고 user 평점 평균이라는 허들도 달라진다. 따라서 작가명이 (정답인 16개가 아니라) 총 35개가 뜨게 된다.
정답
with aut as (
## 작가의 베스트셀러 기록 횟수
SELECT
author,
count(distinct concat(name,"-",year)) as best_years
from books
group by 1
)
, tmps as (
## 픽션 작가의 평균 성적
select
a.author,
avg(user_rating) user_rating,
avg(reviews) reviews,
best_years
from books a
left join aut b on b.author = a.author
where best_years >= 2
and genre = 'Fiction'
group by 1,4
order by 1
)
SELECT
*
from tmps a
left join (select avg(reviews) total_review from books where genre = 'Fiction') b on 1=1
where user_rating >= 4.5
and reviews >= total_review
조금 귀찮아졌지만 신중을 기하기 위해 베스트셀러 기록도 연도+제목의 concat으로 더 꼼꼼히 판별했다.
나는 하나의 서브쿼리 안에서 처리하기보단 CTE를 마구 만드는 편이라 이렇게 풀었는데 좀 더 깔끔하게도 가능할 것 같다.
