[프로그래머스] MySQL Lv.3 전체 정답 풀이

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

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

with rental as(
    select car_id, month(start_date) month
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
    where start_date between '2022-08-01' and '2022-10-31'
)

SELECT month, car_id, count(*) as records
from rental
where car_id in (
    select car_id
    from rental
    group by car_id
    having count(*) >= 5)
group by month, car_id
order by month asc, car_id desc

최종 출력은 월별 + car_id 별 대여 횟수이지만, 중간에 8월부터 10월까지 총 대여 횟수가 5회가 넘어가야 한다는 조건이 붙어 이를 구현하기 위해 where절 내에 서브 쿼리를 하나 더 작성하였다

 

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

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

SELECT concat('/home/grep/src/', board.board_id, '/', file_id, file_name, file_ext) as FILE_PATH
from USED_GOODS_BOARD as board
    inner join USED_GOODS_FILE as file
    on board.board_id = file.board_id
where views = (
    select max(views)
    from USED_GOODS_BOARD)
order by file_id desc

특정 요소의 max/min 인 튜플만 선택하기 위해 where 문 안에 서브 쿼리를 작성해서 위와 같이 처리해주었다

 

헤비 유저가 소유한 장소

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

SELECT id, name, host_id
from places
where host_id in (
    select host_id
    from places
    group by host_id
    having count(*) >= 2)
order by id

2번 이상 등록한 host_id 여부 판별을 위한 where 문 내의 서브 쿼리

 

 

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

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

with car as(
    select distinct car_id,
    case
        when start_date <= '2022-10-16' and end_date >= '2022-10-16' then '대여중'
        else '대여 가능'
    end
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

select car_id,
    case
        when count(*) = 2 then '대여중'
        else '대여 가능'
    end as availabilty
from car
group by car_id
order by car_id desc

위와 같이 작성했는데 너무 복잡하게 작성한 것 같다

추후 좀 더 간결하게 작성할 수 있는 방법을 고민해봐야겠다

 

조건에 맞는 사용자 정보 조회하기

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

SELECT user_id, nickname,
    concat(city, ' ', street_address1, ' ', street_address2) as '전체주소',
    concat(substr(tlno, 1, 3), '-', substr(tlno, 4, 4), '-', substr (tlno, 8, 4)) as '전화번호'
from used_goods_user as user
where user.user_id in (
    select writer_id
    from used_goods_board
    group by writer_id
    having count(*) >= 3)
order by user_id desc

01012345678 형태로 된 전화번호를 010-1234-5678 형태로 바꾸기 위해서 concat과 substr 함수를 이용하였다

 

없어진 기록 찾기

SELECT outs.animal_id, outs.name
from animal_ins as ins right join animal_outs as outs
    on ins.animal_id = outs.animal_id
where ins.datetime is null
order by ins.animal_id

 

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

SELECT distinct car.car_id
from CAR_RENTAL_COMPANY_CAR as car inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY as history
    on car.car_id = history.car_id
where car_type = '세단' and start_date like '2022-10-%'
order by car.car_id desc

 

즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT food_type, rest_id, rest_name, favorites
from rest_info as i
where i.favorites = (
    select max(favorites)
    from rest_info as comp
    where i.food_type = comp.food_type)
order by food_type desc

 

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

SELECT user_id, nickname, sum(price) as total_sales
from used_goods_board as board inner join used_goods_user as user
    on board.writer_id = user.user_id
where status = 'DONE'
group by user_id, nickname
having sum(price) >= 700000
order by total_sales asc

 

조건별로 분류하여 주문상태 출력하기

SELECT order_id, product_id, date_format(out_date, '%Y-%m-%d') as out_date, 
    case
        when out_date is null then '출고미정'
        when out_date > '2022-05-01' then '출고대기'
        else '출고완료'
    end as '출고여부'
from food_order
order by order_id

 

오랜 기간 보호한 동물(2)

SELECT ins.animal_id, ins.name
from animal_ins as ins inner join animal_outs as outs
    on ins.animal_id = outs.animal_id
order by datediff(outs.datetime, ins.datetime) desc
limit 2;

상위 2개 튜플만 출력하기 위해 datediff를 통해 날짜 간의 차이를 구해서 정렬시키고 limit을 사용했다

 

있었는데요 없었습니다

SELECT ins.animal_id, ins.name
from animal_ins as ins inner join animal_outs as outs
    on ins.animal_id = outs.animal_id
where ins.datetime > outs.datetime
order by ins.datetime asc

 

카테고리 별 도서 판매량 집계하기

SELECT category, sum(sales) as total_sales
from book inner join book_sales on book.book_id = book_sales.book_id
where sales_date like '2022-01%'
group by category
order by category asc

 

오랜 기간 보호한 동물(1)

SELECT ins.name, ins.datetime
from animal_ins as ins left join animal_outs as outs
    on ins.animal_id = outs.animal_id
where outs.datetime is null
order by ins.datetime
limit 3;