SQL 코테 연습 프로그래머스 Lv.2 문제풀이

SQL을 계속 안쓰다보면 실력이 녹슬을것 같아서 여러 사이트의 SQL 코딩 테스트를 풀어보고 리뷰해보려고 한다! 일단 시작은 프로그래머스의 Lv.2의 정답률이 낮은 4개의 문제들 부터 시작!!

(문제는 모두 MySQL로 풀어보았다.)

 

1. 재구매가 일어난 상품과 회원 리스트 구하기 (SELECT)

프로그래머스

우선 동일한 상품을 재구매 여부를 확인할 수 있는 기준을 확인해준다. 처음엔 상품판매 ID가 한 구매당 하나로 unique값일줄 알았는데 예시로 보여준 테이블을 확인해보면 ONLINE_SALE_ID이 '2'인 값을 통해 동일 USER_ID, PRODUCT_ID 이면 동일 ONLINE_SALE_ID가 부여되는것을 확인 할 수 있다. 또한 문제 설명을 다시 자세히 읽어보니 동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.라고 써있는 것을 확인 할 수 있다. (역시 문제를 찬찬히 잘 읽어봐야 한다😅)

따라서 상품 재구매 여부는 USER_ID, PRODUCT_ID를 GROUP BY 하여 SALES_DATE를 count하면 될 것으로 보인다. 

SELECT USER_ID, PRODUCT_ID, count(SALES_DATE) AS COUNT_P
FROM ONLINE_SALE
GROUP BY 1,2
ORDER BY 1, 2 DESC
;

 

위의 코드로 동일 상품 재구매를 했다면 COUNT_P가 2이상인것을 확인 할 수 있다. 해당 쿼리를 SELECT문의 서브쿼리로 넣어 WHERE문에서 COUNT_P가 2 이상인 USER_ID, PRODUCT_ID를 추출하면 완료이다. 

SELECT USER_ID, PRODUCT_ID
FROM (SELECT USER_ID, PRODUCT_ID, count(SALES_DATE) AS COUNT_P
        FROM ONLINE_SALE
        GROUP BY 1,2) AS a
WHERE COUNT_P > 1
ORDER BY 1, 2 DESC
;

 

* SQL에서 불편한 점은 SELECT 문에서 새로운 컬럼을 생성했을 때  쿼리 실행 순서 때문에 이를 바로 WHERE문에서 사용 불가하다는 점인것 같다. 

 

 

 

2. 가격대 별 상품 개수 구하기 (GROUP BY)

프로그래머스

 만원 단위의 가격대 별로 상품 개수를 출력하며 가격대 정보는 각 구간의 최소금액을 원하는것으로 보아 값을 절삭해야할 필요가 보이며, 이를 위해 truncate함수를 사용하여 새로운 컬럼을 생성해야할것으로 보인다. (MySQL은 truncate, Oracle은 trunc 함수를 사용한다)

SELECT PRODUCT_ID, PRICE, truncate(PRICE, -4) AS PRICE_GROUP
FROM PRODUCT
;

 

위의 코드로 PRICE의 만원 단위의 가격대로 절삭된것을 확인 할 수 있으며, 새로운 컬럼을 생성했다. 해당 테이블을 서브쿼리를 통해 생성한 새로운 컬럼 PRICE_GROUP을 GROUP BY 하여 PRODUCT_ID를 count하여 각 각겨대별 상품 개수를 세어준다.

SELECT PRICE_GROUP, count(PRODUCT_ID)
FROM (SELECT PRODUCT_ID, PRICE, truncate(PRICE, -4) AS PRICE_GROUP
        FROM PRODUCT) AS a
GROUP BY 1
ORDER BY 1
;

 

 

 

3. 조건에 부합하는 중고거래 상태 조회하기 (String, Date)

프로그래머스

이 문제에서 해결해야 하는 부분은 2022년 10월 5일에 등록된 중고거래 게시물과 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력이다. 첫번째 해결 부분은 단순 동일한 날짜를 찾으면 되기에 날짜 = '원하는 날짜'로 하여 WHERE문을 통해 찾고, 두번째 해결 부분은 CASE WHEN문을 사용하여 새로운 컬럼을 생성해주면 된다. 

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    CASE WHEN STATUS='SALE' THEN '판매중'
        WHEN STATUS='RESERVED' THEN '예약중'
        ELSE '거래완료' END 
        AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC
;

 

 

 

4. 자동차 평균 대여 기간 구하기 (String, Date)

프로그래머스

평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간을 구하고, 이후 추출한 데이터를 여러 조건에 맞게 정렬해야하는 문제이다. 데이터를 확인해보면 대여 기간을 확인하기 위해 대여 종료일과 시작일의 날짜 차이를 확인해야하며 이를 위해 date 함수를 사용해도 되지만 종료일-시작일을 통해 두 날짜의 차이를 확인할 수 있다. 

SELECT *
FROM(SELECT CAR_ID, ROUND(AVG((END_DATE - START_DATE)+1),1) AS AVERAGE_DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID) as a
WHERE AVERAGE_DURATION >= 7
ORDER BY 2 DESC, 1 DESC
;

 

(오답으로 나오는데 무엇이 문제인지 확인 후 수정할 예정이다...)