[MySQL] 그룹 스터디 4일 차 (11/12)

프로그래머스 《자동차 대여 기록 별 대여 금액 구하기》

@ Level 4

@ String, Date

프로그래머스

문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

레벨3이랑 이렇게 차이가 심해도 되나..? 싶은. ...

우선 내가 처음에 잘못 작성하거나 못작성한 부분은

1. DATEDIFF(end_date, start_date) + 1 : 대여 날짜 계산에서 +1을 하지 않음 > 1시간 대여한 경우 0일로 되는 문제

2. COALESCE 이용 > 할인율이 없는 경우 어떻게 처리해야 하는지 생각 못함

3.  마지막 JOIN 에서 car.car_type = plan.car_type > 테이블 결합 이상하게 됨.. 

WITH rental_duration AS (
     SELECT *, CASE
        WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
        WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
        ELSE NULL END AS duration_type
    FROM car_rental_company_rental_history
)
SELECT
    rental.history_id,
    FLOOR(daily_fee * (DATEDIFF(end_date, start_date) + 1) * (100 - COALESCE(plan.discount_rate, 0)) / 100) AS fee
FROM rental_duration rental
LEFT JOIN car_rental_company_car car ON rental.car_id = car.car_id
LEFT JOIN car_rental_company_discount_plan plan
ON rental.duration_type = plan.duration_type AND car.car_type = plan.car_type
WHERE car.car_type = '트럭'
ORDER BY fee DESC, rental.history_id DESC;

이게 최선인지 잘 모르겠지만.. 적어도 나에게는 제일 떠올리기 쉬운 방법인 것 같다

 

 

프로그래머스 《없어진 기록 찾기》

@ Level 3

@ JOIN

프로그래머스

문제
천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

SELECT a.animal_id, a.name
FROM animal_outs a
LEFT JOIN animal_ins b ON a.animal_id = b.animal_id
WHERE b.datetime IS NULL;

 

 

프로그래머스 《물고기 종류 별 대어 찾기》

@ Level 3

@ SUM, MAX, MIN

프로그래머스

문제
물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.
물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.결과는 물고기의 ID에 대해 오름차순 정렬해주세요.단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

SELECT id, fish_name, t2.length
FROM (
    SELECT a.fish_type, b.fish_name, MAX(LENGTH) AS LENGTH
    FROM fish_info a
    JOIN fish_name_info b ON a.fish_type = b.fish_type
    GROUP BY a.fish_type, b.fish_name
) t1
LEFT JOIN fish_info t2 ON t1.fish_type = t2.fish_type
AND t1.length = t2.length
ORDER BY 1;

 

 

 

HackerRank 《Top Competitors》

@ Medium

@ Basic JOIN

https://www.hackerrank.com/challenges/full-score/copy-from/409572012?isFullScreen=false

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

SELECT FS.hacker_id, H.name
FROM (
    SELECT S.hacker_id, COUNT(*) as cnt
    FROM submissions S 
    LEFT JOIN challenges C ON S.challenge_id = C.challenge_id
    LEFT JOIN difficulty D ON C.difficulty_level = D.difficulty_level
    WHERE S.score = D.score
    GROUP BY S.hacker_id
    HAVING COUNT(*) > 1
) FS
JOIN hackers H ON FS.hacker_id = H.hacker_id
ORDER BY FS.cnt DESC, FS.hacker_id ASC;

 

서브쿼리를 쓰지 않아도 가능

SELECT H.hacker_id, H.name
FROM submissions S 
JOIN hackers H ON S.hacker_id = H.hacker_id
JOIN challenges C ON S.challenge_id = C.challenge_id
JOIN difficulty D ON C.difficulty_level = D.difficulty_level
WHERE S.score = D.score
GROUP BY H.hacker_id, H.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, S.hacker_id ASC;