[코드카타 스터디]sql_5(67,75번 문제)

문제링크

67번. https://school.programmers.co.kr/learn/courses/30/lessons/133027

75번. https://school.programmers.co.kr/learn/courses/30/lessons/151141

문제67.
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

1.
SELECT H.FLAVOR
FROM FIRST_HALF H JOIN JULY J 
ON H.FLAVOR = J.FLAVOR
GROUP BY 1
ORDER BY (SUM(H.TOTAL_ORDER+ J.TOTAL_ORDER)) DESC
LIMIT 3;

2.
SELECT flavor
FROM (SELECT flavor, rank() over(order by sum_order desc) as rn
      FROM(
          SELECT a.flavor, sum(a.total_order+b.total_order) sum_order
          FROM first_half a left join july b on a.flavor=b.flavor
          GROUP BY 1) a
     )b
 WHERE rn<=3
 ORDER BY rn
 
 3.
 SELECT flavor
FROM
        (
            (SELECT *
            FROM first_half
            )
        UNION
            (SELECT *
            FROM july
            )
   	    )
GROUP BY flavor
ORDER BY SUM(total_order) DESC
limit 3

---------------------------------------------------------------

select sq1.flavor
from
    (
    select flavor, sum(total_order) tto
    from first_half
    group by flavor
    ) sq1
left join 
    (
    select flavor, sum(total_order) tto
    from july 
    group by flavor
    ) sq2
on sq1.flavor = sq2.flavor 
order by (sq1.tto + sq2.tto) desc
limit 3

4.
SELECT FLAVOR
FROM(   SELECT * FROM FIRST_HALF
        UNION ALL
        SELECT * FROM JULY
    )U1
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

best

SELECT FLAVOR
FROM(   SELECT * FROM FIRST_HALF
        UNION ALL
        SELECT * FROM JULY
    )U1
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

->상반가와 7월의 판매량을 합치는것이라면 조인보다는 유니온을써서 하나의 테이블로 만들고, 중복이 있을 수 있으므로 유니온 올을 사용하여 테이블을 만들고, 이후 총합을 순으로 정렬해주는것이 가장 질문의 의도와 맞다고 생각했다.

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

1.
SELECT HISTORY_ID, 	
       ROUND(DAILY_FEE * (DATEDIFF(H.END_DATE,H.START_DATE)+1)
    * (case 
    when DATEDIFF(END_DATE,START_DATE)+1 < 7 then 1
    when DATEDIFF(END_DATE,START_DATE)+1 < 30 then 0.95
    when DATEDIFF(END_DATE,START_DATE)+1 < 90 then 0.92
    else 0.85 end))  "FEE"
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '트럭'
GROUP BY 1
ORDER BY 2 DESC, 1 DESC;

2.
SELECT b.history_id, 
       round(a.daily_fee*(datediff(b.end_date, b.start_date)+1)*(100-if(c.discount_rate is null, 0, c.discount_rate))/100) as fee
FROM CAR_RENTAL_COMPANY_CAR a LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b 
															ON a.car_id = b.car_id
                              LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c 
                              ON a.car_type=c.car_type
        AND c.duration_type = (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)
WHERE a.car_type = '트럭'
ORDER BY 2 desc, 1 desc

3.
with sq1 as (
SELECT h.history_id, c.daily_fee, c.car_id, 
       datediff(end_date, start_date)+1 rent_day
FROM CAR_RENTAL_COMPANY_CAR c 
inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id                             
WHERE c.car_type = '트럭'
)
,    sq2 as (
select duration_type - substr(duration_type, -4, 4) dur, discount_rate
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
where car_type = '트럭'
)

select history_id, round(daily_fee * rent_day * (100-disc_rate)/100) total
from (
select history_id, daily_fee, rent_day, ifnull(max(dur), 'under_7') dur, ifnull(max(discount_rate), 0) disc_rate
from sq1 left join sq2 on rent_day >= dur 
group by history_id, daily_fee, rent_day
) sq3
order by 2 desc, 1 desc

4.
SELECT Q3.HISTORY_ID, 
        FLOOR((100 - IFNULL(DISCOUNT_RATE,0)) /100 * FEE) AS FEE
FROM (  SELECT Q1.CAR_ID, Q2.CAR_TYPE, Q1.HISTORY_ID,
            DAYS * DAILY_FEE AS FEE,
            CASE
                WHEN DAYS >= 90 THEN '90일 이상'
                WHEN DAYS >= 30 THEN '30일 이상'
                WHEN DAYS >= 7 THEN '7일 이상'
                ELSE NULL END AS DURATION_TYPE
        FROM(SELECT *, DATEDIFF(end_date, start_date) + 1 AS DAYS
             FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            ) Q1
        INNER JOIN
            (SELECT * 
             FROM CAR_RENTAL_COMPANY_CAR
             WHERE CAR_TYPE = '트럭'
            ) Q2
        ON Q1.CAR_ID = Q2.CAR_ID
    )Q3
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN T1
ON (Q3.CAR_TYPE = T1.CAR_TYPE) AND (Q3.DURATION_TYPE = T1.DURATION_TYPE) 
ORDER BY FEE DESC, HISTORY_ID DESC

best

SELECT b.history_id, 
       round(a.daily_fee*(datediff(b.end_date, b.start_date)+1)*(100-if(c.discount_rate is null, 0, c.discount_rate))/100) as fee
FROM CAR_RENTAL_COMPANY_CAR a LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b 
															ON a.car_id = b.car_id
                              LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c 
                              ON a.car_type=c.car_type
        AND c.duration_type = (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)
WHERE a.car_type = '트럭'
ORDER BY 2 desc, 1 desc

-> 조인의 on절에 케이스구문을 넣어 조건을 성립시켜주는식의 접근방법이 새로웠다.

사용문법

• UNION / UNION ALL
• DATEDIFF
• JOIN ON 에서 CASE WHEN 사용해서 조인
• duration_type - substr(duration_type, -4, 4)
  • substr(duration_type, 1, length(duration_Type) - 4)