SQL 코드카다 61번
left join을 쓸 때
기준이 되는 테이블 / 합쳐올 테이블 구분 잘 하기.
별점이 없는 식당도 있을 수 있으므로 rest_info 테이블에 rest_review를 갖다 붙여야 함.
SQL 코드카타 62번
두 날짜의 차이를 계산할 때
datediff 함수를 쓰는데, 이 때 계산된 결과는
단순한 일 차이(e.g. 4/7과 4/8을 넣고 계산하면 1을 뱉음)이므로 이 결과에 +1을 해 줘야 문제에서 요구하는 대여기간을 얻을 수 있음.
※ timestampdiff를 써도 마찬가지임.
SQL 코드카타 64번
서브쿼리 기본 활용 문제
SQL 코드카타 65번
한 개의 row에 한 개의 값만 있는 컬럼에서
2개의 서로 다른 값을 갖는 row를 골라내는 문제.
해결하는 방법은 여러 가지가 있는데,
내가 낸 답은 이거였다.
SELECT cart_id
FROM CART_PRODUCTS
where name in ('milk','yogurt')
group by cart_id having count(distinct name) >= 2
order by 1;저렇게 하면 cart_id별로 그루핑하되,
서로 다른 이름(이 경우엔 milk와 yogurt)이 2개 이상인 경우를 불러오게 되므로 정확히 문제에서 요구하는 바와 같다.
서브쿼리를 활용해 풀게 되면
SELECT cart_id
FROM CART_PRODUCTS
WHERE name = 'Milk' AND cart_id IN (
select cart_id
from cart_products
where name = 'Yogurt')
GROUP BY cart_id
ORDER BY 1이런 답을 낼 수도 있는데,
이건 마침 문제에서 milk와 yogurt라는 2개의 값만 주었기 때문에 가능한 풀이가 아닐까 싶다. 만약에 milk, yogurt외에 2~3개 정도의 값을 더 뽑으라고 했으면 서브쿼리를 여러 번 AND로 연결시키며 중첩해야 하지 않을까..? 개인적으로는 첫 번째 풀이가 복잡한 조건 하에서도 깔끔하게 원하는 결과를 얻을 수 있을 것 같음.
SQL 코드카타 66번
서브쿼리 기본 활용 문제
where 절 내에서 서브쿼리를 써서
'가장 큰 view 수를 갖는 게시물'을 먼저 정의해 준 후,
그걸 전제로 깔고 나머지 필요한 연산을 해 주면 된다.
SQL 코드카타 67번
처음에는 left join을 쓰는 간단한 문제인 줄 알고
select j.flavor
from JULY j
left join FIRST_HALF h on j.shipment_id = h.shipment_id
group by j.flavor
order by sum(j.total_order+h.total_order) desc
limit 3이렇게 제출했는데 오답이 떴다.
알고 보니 flavor를 기준으로 group by를 하면서
JULY 테이블에는 있고 FIRST HALF 테이블에는 없는,
shipment ID 209번의 결과가 합산되지 않은 것.
나는 order by 뒤에 if문을 써서 해결했다.
select j.flavor
from JULY j
left join FIRST_HALF h on j.shipment_id = h.shipment_id
group by j.flavor
order by sum(if(h.total_order is null,j.total_order,j.total_order+h.total_order)) desc
limit 3;다른 분들의 정답을 보니
1. 먼저 7월의 판매량을 맛별로 그루핑해 놓고 (shipment ID가 아니라 맛별로 그루핑하므로 놓치는 데이터는 없게 된다.)
2. 그걸 상반기 데이터와 맛별로 inner join해서 총 판매량을 구하는 방식도 있더라.
코드로 표현하자면 이런 식.
SELECT f.FLAVOR
FROM FIRST_HALF f
INNER JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS "TOTAL_ORDER"
FROM JULY
GROUP BY FLAVOR) j
ON f.FLAVOR = j.FLAVOR
ORDER BY f.TOTAL_ORDER + j.TOTAL_ORDER DESC
LIMIT 3;내 풀이보다 이쪽이 더 깔끔해 보인다.
SQL 코드카타 68번
left join을 연습할 때
기준이 되는 테이블을 뭘로 해야 할지 아리까리하다면
이 문제를 연습해 보면 좋을 것 같다.
여기서는 '작가별'로 매출액 집계를 해야 하므로,
기준이 되는 테이블은 author가 되어야 한다.
그리고 한 작가가 여러 카테고리의 책을 발행할 수도 있으므로,
group by의 적용도 author에 대해서만 하는 게 아니라 category로도 해 줘야 함. 아래는 내가 제출한 정답.
select a.author_id, a.author_name, b.category,
sum(b.price*s.sales) TOTAL_SALES
from AUTHOR a
left join BOOK b on a.author_id = b.author_id
left join BOOK_SALES s on b.book_id = s.book_id
where date_format(s.sales_date,'%Y-%m') = '2022-01'
group by a.author_id, b.category
order by a.author_id, b.category desc;SQL 코드카타 69번
아쉬운 번역 때문에 한참 삽질했던 문제.
"특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요."
라고 되어 있었는데, 나는 이걸 아예 결과 출력에서 제외하라는 뜻으로 이해했었다. 그런데 알고 보니 0이나 null로 보이게끔 출력하지 말라는 얘기였음.
select month(start_date) MONTH, car_id, count(*) 'RECORDS'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where (start_date between '2022-08-01' and '2022-10-31')
and (car_id in
(select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where start_date between '2022-08-01' and '2022-10-31'
group by car_id having count(*)>=5))
group by 1, 2
order by 1 asc, 2 desc;풀이 자체는 어렵지 않다.
where 절 안에 서브쿼리를 써서
1. car_id 기준으로 그루핑한 후 5회 이상인 car_id만 뽑아내고
2. 본 쿼리의 where 절에서 그걸 받아서 나머지 연산을 해 주면 됨
SQL 코드카타 70번
사실 위의 69번보다 훨씬 애매한 문제.
개인적으로 이 문제는 출제 오류라고 본다.
우선 내가 제출해서 정답으로 인정된 코드다.
select m.MEMBER_NAME, r.REVIEW_TEXT, date_format(r.REVIEW_DATE,'%Y-%m-%d') as 'REVIEW_DATE'
from REST_REVIEW r
inner join MEMBER_PROFILE m on r.member_id = m.member_id
where r.member_id =
(select member_id from rest_review
group by member_id
order by count(REVIEW_SCORE) desc
limit 1)
order by 3, 2;이 쿼리를 실행하면 김민재의 결과만 나오는데,
사실 리뷰를 가장 많이 작성한 유저는 모두 3명이다 (즉, 공동 1등이 있는 셈)
따라서 서브쿼리 내에서 limit 1로 처리하면
공동 1등 세 명 중에 가장 결과가 빠른 김민재만 뜨게 되는데,
엄밀히 말하면 다른 두 명의 결과도 모두 쿼리로 조회되게 만들어야 정답일 것이다.
가장 많이 작성한 회원의 리뷰를 조회하라고 했지,
가장 많이 작성한 회원 1명의 리뷰를 조회하라고 하지는 않았으므로.
보니까 3명 결과를 모두 뜨게 한 결과도 정답이고
나처럼 처리한 결과도 정답으로 처리해주긴 하던데,
정확하게는 내가 낸 저 쿼리는 오답이어야 한다.
아무튼 문제의 퀄리티와는 별개로,
원래 서브쿼리를 쓸 때 in을 쓰려고 했는데
in과 limit를 함께 쓰지 못한다는 사실을 알게 돼서
같은(=)으로 처리하고 inner join을 적용했다.
in과 limit를 함께 쓰기 위해 alias를 쓰는 방법도 있다고 하는데 여러 가지로 시도해 봤지만 성공하진 못했다.
이 문제를 나중에 다시 들여다보게 된다면
세 가지를 복습하는 의미를 갖게 되겠다.
1. 서브쿼리를 쓸 때 in과 limit를 같이 쓰려면 제약이 있다.
2. 내가 낸 반쪽짜리 정답 말고, 공동 1등이 여러 명일 때 결과를 출력하는 찐 정답 쿼리는 완성하지 못했다. 다른 사람들의 풀이를 봐도 이해하기 어려워서 나중에 다시 공부할 때 들여다봐야지.
3. 문제를 엄밀하고 정확하게 정의하는 것이 중요하다. 아마도 실무에서 마주치게 될 대부분의 현업들이 이 70번 문제처럼 애매하게 데이터 조회를 요청해 올 거라고 본다. 이 때 데이터 분석가가
- 공동 1등이 여러 명인데 다같이 뽑으면 되는 건지
- 아니면 다른 기준이 있으면 적용해서 1명만 뽑아야 하는건지
를 집요하게 물어봐서 clear하게 만들어 놓아야 한다. 그래야 시간낭비가 없게 될 것.
