프로그래머스 SQL 76문제 풀기 ( 정답률 높은 순 기준 6개 ) - 5

LV.4 오프라인/온라인 판매 데이터 통합하기(정답률 : 60%) 🌟🌟🌟🌟🌟 UNION함수

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

(SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE AS N
WHERE SALES_DATE LIKE '2022-03%'
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d")AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE AS F
WHERE SALES_DATE LIKE '2022-03%')
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

LV.4 입양 시각 구하기(2)(정답률 : 59%) 🌟🌟🌟🌟🌟 SET함수

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

참고링크

SET @HOUR := -1; # 변수선언

SELECT (@HOUR := @HOUR +1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

SQL에서 `SET` 문은 변수를 설정하거나 값을 할당하는 데 사용됩니다. `SET`을 사용하여 변수를 선언하고 값을 할당하면 해당 변수는 쿼리 내에서 사용할 수 있습니다. 

예를 들어, 주어진 코드에서 `@HOUR`이라는 변수를 선언하고 `-1`로 초기화하고 있습니다. 그 후 `SELECT` 문에서 이 변수를 사용하여 `@HOUR` 변수의 값을 증가시키고 있습니다.

이렇게 변수를 설정하면 해당 변수는 쿼리 실행 중에 임시로 사용되며 결과 집합에는 영향을 미치지 않습니다. 즉, `@HOUR` 변수는 임시로 쿼리 내에서만 사용되고 데이터베이스에 영구적으로 저장되지 않습니다.

또한, 주어진 코드에서는 시간대(`HOUR`)를 기준으로 `ANIMAL_OUTS` 테이블에서 각 시간대별로 동물의 수를 계산하고 있습니다. 결과 집합에는 `HOUR`과 해당 시간대의 동물 수(`COUNT`)가 표시됩니다. 이렇게 변수를 사용하여 쿼리의 결과를 계산하거나 다양한 목적으로 활용할 수 있습니다.

SET @HOUR := -1; # 변수선언

SELECT (@HOUR := @HOUR +1) AS HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23

 

LV.1 조건에 부합하는 중고거래 댓글 조회하기 (정답률 : 58%) 🌟

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

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B
ON A.BOARD_ID = B.BOARD_ID
WHERE A.CREATED_DATE LIKE '2022-10%'
ORDER BY CREATED_DATE, TITLE

 

 

LV.4 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(정답률 : 41%) 🌟🌟🌟🌟🌟어려움

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

 

참고 링크

세상에 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하다라는 게 제일 어려웠다.

다른 풀이 보니까 11월 1일보다 종료날짜가 크고, 12월 1일보다 시작날짜가 작은 걸로 풀어다.

 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차는, 30일간 원금 금액에서 할인금액 제외한 FEE를

HAVING절에서 조건을 걸어주면 된다.

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID=H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE=P.CAR_TYPE
WHERE C.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'
) AND P.DURATION_TYPE='30일 이상'
GROUP BY C.CAR_ID
HAVING C.CAR_TYPE IN ('세단', 'SUV') AND (FEE>=500000 AND FEE<2000000) 
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

SELECT c.car_id, c.car_type,  round(c.DAILY_FEE * 30*(100-p.DISCOUNT_RATE)/100) as fee
from CAR_RENTAL_COMPANY_CAR c join CAR_RENTAL_COMPANY_DISCOUNT_PLAN p on c.CAR_TYPE = p.CAR_TYPE
where c.CAR_TYPE in ('세단', 'SUV')
and c.car_id not in 
(
    SELECT car_id
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
    where END_DATE >= '2022-11-01' and START_DATE <= '2022-11-30'
)
and DURATION_TYPE = '30일 이상'
group by c.car_id
having (fee >= 500000 and fee < 2000000)
order by fee desc,  C.CAR_TYPE, c.car_id desc

LV.4 자동차 대여 기록 별 대여 금액 구하기 (정답률 : 41%) 🌟🌟🌟🌟🌟WITH구문

 

참고 링크

할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

이부분응ㄹ 위해서 with구문을 사용한다.

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    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 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   



SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC

 

 

LV.5 상품을 구매한 회원 비율 구하기 (정답률 : 40%) 🌟🌟🌟🌟🌟 어려움

참고 링크

SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
	ROUND((COUNT(*)/ (SELECT COUNT(*)
					FROM USER_INFO WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM (
    SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.USER_ID
    FROM ONLINE_SALE S
    JOIN USER_INFO U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021
) A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH