[코드카타 스터디]SQL_3(57,59번 문제)

문제링크

63번

https://school.programmers.co.kr/learn/courses/30/lessons/157342

70번

https://school.programmers.co.kr/learn/courses/30/lessons/131124

문제
63번
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

쿼리문 비교

1.
# datediff 함수를 사용하면 사이의 기간만 반환
# 빌린 날짜를 추가해야 하므로 +1을 해줌 (빌리는 순간 날짜카운트)
# 다행히 start_date와 end_date가 얽혀 연산되는 경우는 없었습니다. 
select CAR_ID,START_DATE,END_DATE, DATEDIFF(END_DATE, START_DATE)+1 as diffdate 
from  CAR_RENTAL_COMPANY_RENTAL_HISTORY
;

select CAR_ID,round(avg(DATEDIFF(END_DATE, START_DATE)+1),1) as AVERAGE_DURATION
from  CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by CAR_ID
having round(avg(DATEDIFF(END_DATE, START_DATE)+1),1)>=7
order by AVERAGE_DURATION desc, CAR_ID desc

2. 
SELECT car_id, round(avg(datediff(end_date,start_date)+1), 1) average_duration
FROM car_rental_company_rental_history
GROUP BY car_id
HAVING average_duration >= 7
ORDER BY 2 DESC, 1 DESC;

3.
SELECT car_id, round(avg(datediff(end_date, start_date)+1), 1) avg_duration
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
having avg_duration >= 7
order by 2 desc, 1 desc

4. 
SELECT CAR_ID, ROUND(AVG((DATEDIFF(END_DATE, START_DATE) + 1)), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY 2 DESC, 1 DESC

best

SELECT CAR_ID, ROUND(AVG((DATEDIFF(END_DATE, START_DATE) + 1)), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY 2 DESC, 1 DESC

음, 데이디프 계산이 조금 헷갈리는것 같다.
기준이 명확히 제시되지 않아서 더 헷갈렸고, 반드시 문제를 풀기전에 중복데이터나 중첩되는 것이 있는지 파악을 하고 쿼리를 작성하는게 좋을 것 같다.

문제
70번
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

쿼리문 비교

1.
SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d')REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE R.MEMBER_ID = (SELECT MEMBER_ID
                     FROM REST_REVIEW
                     GROUP BY 1
                     ORDER BY COUNT(*) DESC
                     LIMIT 1)
ORDER BY 3, 2;

2. 
SELECT a.member_name, b.review_text, 
       date_format(review_date, '%Y-%m-%d') as review_date
FROM member_profile a LEFT JOIN rest_review b on a.member_id=b.member_id
WHERE b.member_id = (SELECT member_id FROM
                                     (SELECT member_id, count(review_id)
                                      FROM rest_review
                                      GROUP BY 1
                                      ORDER BY 2 desc
                                      LIMIT 1) c )
ORDER BY 3, 2;

3. 
select sq2.member_name, rr.review_text, date_format(rr.review_date, '%Y-%m-%d')
from rest_review rr inner join
    (
    select p.member_name, p.member_id
    from member_profile p left join 
        (
        select member_id, rank() over(order by count(*) desc) rn
        from rest_review
        group by member_id
        ) r
    on p.member_id = r.member_id
    WHERE rn = 1
    ) sq2
on rr.member_id = sq2.member_id
order by 3, 2

4. 
SELECT MEMBER_NAME, REVIEW_TEXT, SUBSTR(REVIEW_DATE,1,10)
FROM MEMBER_PROFILE T1
INNER JOIN 
    (   SELECT MEMBER_ID, REVIEW_TEXT, REVIEW_DATE
        FROM REST_REVIEW
        WHERE MEMBER_ID = 
            (   SELECT MEMBER_ID
                FROM REST_REVIEW
                GROUP BY MEMBER_ID
                ORDER BY COUNT(*) DESC
                LIMIT 1
            )
    )S1
ON T1.MEMBER_ID = S1.MEMBER_ID
ORDER BY 3, 2

best

select sq2.member_name, rr.review_text, date_format(rr.review_date, '%Y-%m-%d')
from rest_review rr
inner join
    (
    select p.member_name, p.member_id
    from member_profile p left join 
	  (
        select member_id, rank() over(order by count(*) desc) rn
        from rest_review
        group by member_id
    ) r
    on p.member_id = r.member_id
    WHERE rn = 1
    ) sq2
    on rr.member_id = sq2.member_id
order by 3, 2

문제에서는 가장 리뷰를 많이쓴 사용자의 리뷰텍스트라고 말했는데 리뷰를 가장 많이 쓴 사람은 여러명인것을 확인했다.
그렇다면 limit을 쓰는 것이 아니라 전체를 구하는게 맞다고 판단했고, 위의 쿼리만 모든 검색을 출력해준다.

사용문법

63번.

• datediff
• join: 두 테이블을 합쳐준다.
• order by count(*) desc; order by 에 집계함수 사용가능!

70번.

• 서브쿼리
• rank() over(order by)