[SQL 코테] 프로그래머스 Lv.3 문제풀이(by MySQL)

1. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 - Lv.3

-- 조회수가 가장 높은 게시물에 대한 첨부파일 경로 조회
SELECT concat("/home/grep/src/", b.BOARD_ID, "/", f.FILE_ID, f.FILE_NAME, f.FILE_EXT) as FILE_PATH
from USED_GOODS_FILE f join (select b2.BOARD_ID 
                             from USED_GOODS_BOARD b2
                             order by b2.VIEWS DESC
                             limit 1) b
on b.BOARD_ID = f.BOARD_ID
order by f.FILE_ID DESC

 

2. 조건에 맞는 사용자 정보 조회하기 - Lv.3

-- 게시물을 3건 이상 등록한 사용자의 정보 조회
SELECT u.USER_ID, u.NICKNAME, concat(u.CITY, " ", STREET_ADDRESS1, " ", STREET_ADDRESS2) as 전체주소, concat(substring(u.TLNO, 1, 3), "-", substring(u.TLNO, 4, 4), "-", substring(u.TLNO, -4)) as 전화번호 
from USED_GOODS_USER u join (select WRITER_ID 
                             from USED_GOODS_BOARD
                             group by WRITER_ID
                             having count(*) >= 3) w
on u.USER_ID = w.WRITER_ID
order by u.USER_ID DESC

 

3. 조건에 맞는 사용자와 총 거래금액 조회하기 - Lv.3

-- 완료된(status=DONE) 중고거래 총금액이 70만원 이상인 사람의 정보 조회
SELECT u.USER_ID, u.NICKNAME, b.total as TOTAL_SALES 
from USED_GOODS_USER u join (select b2.WRITER_ID as id, sum(b2.PRICE) as total
                             from USED_GOODS_BOARD b2
                             where b2.STATUS = "DONE"
                             group by b2.WRITER_ID
                             having sum(b2.PRICE) >= 700000) b
on u.USER_ID = b.id
order by b.total

 

4. 특정 옵션이 포함된 자동차 리스트 구하기 - Lv.1

-- OPTIONS에 해당 옵션이 포한되어 있는 정보 조회
SELECT c.CAR_ID, c.CAR_TYPE, c.DAILY_FEE, c.OPTIONS
from CAR_RENTAL_COMPANY_CAR c
where c.OPTIONS like "%네비게이션%" 
order by c.CAR_ID DESC

 

5. 자동차 평균 대여 기간 구하기

-- 평균 대여 기간(시작일, 종료일 주어짐)이 7일 이상인 자동차의 정보 조회
select h.CAR_ID, round(avg(DATEDIFF(h.END_DATE, h.START_DATE)+1), 1) as AVERAGE_DURATION
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
group by h.CAR_ID
having AVERAGE_DURATION >= 7
order by AVERAGE_DURATION desc, h.CAR_ID desc

• DATEDIFF(종료일, 시작일)+1: 기간 구하기
• ROUND(값, 최종 자리수): 반올림

6. 대여 기록이 존재하는 자동차 리스트 구하기

-- 자동차 종류가 '세단'인 자동차들 중 10월에 대여 시작한 기록이 있는 자동차 리스트 조회
select distinct c.CAR_ID 
from (select c2.CAR_ID
      from CAR_RENTAL_COMPANY_CAR c2
      where c2.CAR_TYPE = "세단") c join (select h2.CAR_ID as id, h2.START_DATE as start_date
                                         from CAR_RENTAL_COMPANY_RENTAL_HISTORY h2 
                                         ) h
on c.CAR_ID = h.id  
where h.start_date like "%-10-%"   
order by c.CAR_ID desc

 

7. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

-- 2022-10-16에 대여 가능 여부 조회
select distinct CAR_ID, case 
                   when CAR_ID in (select c.CAR_ID
                                   from CAR_RENTAL_COMPANY_RENTAL_HISTORY c
                                   where '2022-10-16' between START_DATE and END_DATE)
                   then '대여중'
                   else '대여 가능'
               end AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
order by CAR_ID desc

 

8. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기***

-- 자동차 종류 일치, 11월 대여 가능, 대여 금액(할인률 적용) 범위, 정렬하여 조회

SELECT car.car_id,car.CAR_TYPE,ROUND(car.DAILY_FEE * 30 * (100 - p.DISCOUNT_RATE) / 100, 0) AS FEE 
FROM CAR_RENTAL_COMPANY_CAR as car 
join (select * from CAR_RENTAL_COMPANY_DISCOUNT_PLAN where DURATION_TYPE ='30일 이상') p 
on car.CAR_TYPE = p.CAR_TYPE
where car.CAR_TYPE in ('세단','suv')
and car.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'
)
group by car.car_id
having FEE BETWEEN 500000 AND 2000000
order by FEE DESC,CAR_TYPE ,CAR_ID DESC