SQL 프로그래머스 코딩테스트 LEVEL 3

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

SELECT animal_ins.name, animal_ins.datetime from animal_ins
left join animal_outs on
animal_ins.animal_id = animal_outs.animal_id
where animal_outs.datetime is NULL
order by animal_ins.datetime asc
limit 3

핵심 키워드
left join -> 2개 테이블 조인
order by -> 시설로 온 동물 날짜순 정렬
limit -> 출력 제한

• 있었는데요 없었습니다

SELECT animal_ins.animal_id,animal_ins.name from animal_ins
left join animal_outs on
animal_ins.animal_id = animal_outs.animal_id
where animal_ins.datetime>animal_outs.datetime
order by animal_ins.datetime asc

animal_id를 기준으로 2개의 테이블을 animal_ins를 기준으로 left 조인하는 것이 핵심
대체적으로 무난했던 문제

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

SELECT animal_ins.animal_id,animal_ins.name 
    #, datediff(animal_outs.datetime,animal_ins.datetime) 
 from animal_ins
left join animal_outs on
animal_ins.animal_id = animal_outs.animal_id
order by datediff(animal_outs.datetime,animal_ins.datetime) desc
limit 2

datediff를 통해 날짜 연산을 할 수 있는지와 limit을 활용할 수 있는지가 핵심!!

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

SELECT order_id, product_id, substring(out_date,1,10) as OUT_DATE,
(
    case when out_date is NULL then '출고미정'
        when substring(out_date,1,10)<='2022-05-01' then '출고완료'
        else '출고대기' end
) as '출고여부'
from food_order

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

select book.category, sum(book_sales.sales) as TOTAL_SALES from book_sales 
left join book on
book.book_id = book_sales.book_id
where book_sales.sales_date like '%2022_01%'
group by category
order by category asc

무난 했던 문제

• 없어진 기록 찾기
  기존 처럼 left join을 하면 안된다
  여기선 animal_id 를 기준으로 손실난 데이터를 확인하기 때문에
  animal_id를 기준으로 전체를 join하는것이 필요하다
  구글링 결과 FULL OUTER JOIN이라는 것이 있다는 것을 알게 되었다.
  https://doh-an.tistory.com/30

하지만 SQL에선 FULL OUTER JOIN을 지원하지 않는다

https://gywlsp.github.io/mysql/5/
-> 하지만 LEFT JOIN과 RIGHT JOIN을 UNION으로 GKQ친따면 FULL OUTER JOIN과 동일한 결과를 낼 수 있다.

with table1 as (
    (
        select animal_ins.animal_id as input_animal_id,
                animal_ins.name as input_animal_name,
                animal_ins.datetime as input_animal_datetime,
                animal_outs.animal_id as output_animal_id,
                animal_outs.name as output_animal_name,
                animal_outs.datetime as output_animal_datetime
            from animal_ins
        left join animal_outs on
        animal_outs.animal_id = animal_ins.animal_id
        order by animal_ins.animal_id
    )
    union
    (
        select animal_ins.animal_id as input_animal_id,
                animal_ins.name as input_animal_name,
                animal_ins.datetime as input_animal_datetime,
                animal_outs.animal_id as output_animal_id,
                animal_outs.name as output_animal_name,
                animal_outs.datetime as output_animal_datetime
            from animal_ins
        right join animal_outs on
        animal_outs.animal_id = animal_ins.animal_id
        order by animal_ins.animal_id
    )
)
select output_animal_id,output_animal_name from table1
where input_animal_id is NULL and output_animal_id is not NULL
order by output_animal_id asc

Union을 사용해서 outer full join을 사용하는 것이 핵심이다!!!

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

1. 음식점 별로 group by로 그룹화 한것에 조건 생성 필요
   구글링 결과 having 절에 대해 알게됨
   https://tadaktadak-it.tistory.com/52
   having 절을 이용하면 그룹화한 후 조건을 적용이 가능할 것 같다
   

여전히 문제가 발생한다. 일식을 보면 112가 FAVORITES라고 나오지만 이미 230이라는 VALUE가 존재한다.

문제의 원인은 GROUP화가 먼저 진행되며 각 카테고리 별로 처음인 VALUE로 먼저 채워지기 때문으로 추정된다.
따라서 ORDER BY로 정렬된 테이블을 WITH로 만든이후 진행하는 것이 이상적으로 보인다.

예상과 다르게 이번에도 의도한데로 쿼리가 먹지 않았다...
아무래도 having 절을 이용하거나 group by의 다른 옵션이 없는지 구글링 해봐야 할 것 같다

?? 생각보다 의외의 결과가 나온다

단순히 그룹화만 하였음에도 불구하고 쿼리가 그룹별로 하나씩만 출력된다... 내가 그동안 착각 했던 것일까?

생각을 약간 전환해 보았다
차라리 각 그룹의 max 값들로 이루어진 테이블을 만들고
2개의 테이블을 조인한 이후 favorites = max(favorites)인 것을 출력하는 쿼리로 진행하면 어떨까?

정답 코드 
with table1 as(
    select food_type,max(favorites) as favorites from rest_info
    group by food_type
)
select rest_info.food_type,rest_info.rest_id,rest_info.rest_name,rest_info.favorites
from rest_info
left join table1 on 
table1.food_type = rest_info.food_type
where table1.favorites = rest_info.favorites
order by rest_info.food_type desc

이 문제는 having 절을 사용할 필요가 없었다.....

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

1. 완료된 중고거래 총금액이 70만원 이상
2. 회원 id, 닉네임, 총거래금액
3. 총거래금액 기준 오름차순

틀린코드
SELECT used_goods_user.user_id,used_goods_user.nickname, used_goods_board.price as TOTAL_SALES from used_goods_board
left join used_goods_user on
used_goods_board.writer_id = used_goods_user.user_id
where status = 'DONE' AND used_goods_board.price >=700000
# SELECT count(*) from used_goods_user

여기서 하나 놓친 것이 있다...

거래금액이 아닌 총 거래 금액이라는 것이다!!!

// 정답 코드
with table1 as (
    SELECT used_goods_board.writer_id, sum(price) as writer_transaction_price 
        from used_goods_board
    left join used_goods_user on
        used_goods_board.writer_id = used_goods_user.user_id
    where status = 'DONE' 
    group by used_goods_board.writer_id
    # SELECT count(*) from used_goods_user
), table2 as (
    SELECT * from used_goods_board
    left join used_goods_user on
        used_goods_board.writer_id = used_goods_user.user_id
    where status = 'DONE' 
    group by used_goods_board.writer_id
)
select table2.user_id,table2.nickname, table1.writer_transaction_price as TOTAL_SALES
    from table2
left join table1 on
table2.writer_id = table1.writer_id
where writer_transaction_price >=700000
order by TOTAL_SALES asc

1. 총거래금액 컬럼을 가지는 테이블을 하나 생성한다

2. writer id를 기준으로 used_goods_board와 used_goods_user 테이블을 조인한 테이블을 생성한다.

3. 생성한 2 테이블을 조인하여 총 거래금액이 70 만원 이상인 사람의 출력을 뽑아낸다!!!
   

완료!!!

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

# 1. 기록 테이블에 자동차 정보 테이블 조인
# 2. 자동차 종류가 세단이고 10월에 대여 시작 기록 있는 자동차 출력
# 3. 자동차 id 중복없이 출력
# 4. 자동차 id 기준 내림차순

select DISTINCT car_rental_company_car.car_id as CAR_ID from car_rental_company_rental_history 
left join car_rental_company_car on 
car_rental_company_rental_history.car_id = car_rental_company_car.car_id
where car_type = '세단' and start_date like '2022-10%'
order by car_rental_company_car.car_id desc

이 문항은 distinct를 통해 중복을 제거할 수 있는지가 핵심

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

# 1. used_goods_board에 used_goods_users 조인 O
# 2. 중고 거래 게시물 3건이상 등록한 사용자 체크 O
# 3. 해당 사용자의 id, 닉네임, 전체주소, 전화 번호 조회
# 3-1 . 문자열 합치는 함수
# https://gent.tistory.com/437
# 4. 전화번호는 xxx-xxxx-xxxx 형태로 형변환
# 5. 회원 id를 기준으로 내림차순
# 6. 중복이 없어야한다!!!

정체주소 부분을 +기호를 사용하였지만 다음과 같이 숫자로 출력된다.
다른 방법으로 전환이 필요하다
concat을 사용하자 정상적으로 출력 됨을 확인 가능하다

concat 함수 사용법 -> https://jhnyang.tistory.com/369

정답 코드 
with table1 as (
    select * from used_goods_board as ugb
    left join used_goods_user as ugu on
    ugb.writer_id = ugu.user_id
), table2 as (
    select ugb.writer_id, count(*) as enroll from used_goods_board as ugb
    left join used_goods_user as ugu on
    ugb.writer_id = ugu.user_id
    group by ugb.writer_id
)
select table1.writer_id as USER_ID,table1.nickname, 
        concat(table1.CITY,' ',table1.street_address1,' ',table1.street_address2) 
        as 전체주소,(concat(substring(tlno,1,3),'-',substring(tlno,4,4),'-',substring(tlno,8,4))) as 전화번호
from table1 
left join table2 on
table1.writer_id = table2.writer_id
where enroll >=3
GROUP BY table1.user_id
order by table1.writer_id desc

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

# 1. CAR_RENTAL_COMPANY_RENTAL_HISTORY 단일 데이터 사용
# 2. 기준일 2022-10-16 대여중인 경우 대여중, 그외는 대여가능 case 문 표시 컬럼은 AVAILABILITYB
# 2-1. 10-16일 대여 불가능한 차들의 목록 테이블을 생성 -> join한 이후 null인 것이 대여가능
# 3. 기준일날 반납 날짜 일경우 대여중으로 표시
# 4. 자동차 id와 AVAILABILITY 출력
# 5. 자동차 ID를 기준 내림차순

with table1 as(
    SELECT crp.car_id,'대여중' as rental_able FROM car_rental_company_rental_history as crp
        where !((substring(crp.start_date,1,10) < '2022-10-16' and substring(crp.end_date,1,10)  < '2022-10-16') or
            (substring(crp.start_date,1,10) > '2022-10-16' and substring(crp.end_date,1,10)  > '2022-10-16') )
    group by crp.car_id
    order by crp.car_id
) , table2 as(
    select * from car_rental_company_rental_history
)
select DISTINCT table2.car_id
,(
    case when table1.rental_able is NULL then '대여 가능'
    else table1.rental_able end
) as AVAILABILITY
from table2
left join table1 on
table1.car_id = table2.car_id
ORDER BY table2.car_id desc

전체적으로 무난했던 문제였던 것같다

핵심 sol
자동차 대여 불가능 시간 =
! (( 대여시작 & 대여 종료 ) <- 2022-10-16 -> ( 대여시작 & 대여 종료 ))

++ 테이블을 여러개로 분할하는 것이 문제를 좀 더 쉽게 풀게 한것 같다.

• 헤비 유저가 소유한 장소

# 1. place 단일 테이블 사용
# 2. 헤비 유저 = 공간을 둘이상 등록
# 3. 헤비 유저 공간 정보 아이디순 조회

with table1 as (
    select host_id,count(*) as space_count from places
    group by host_id
)
select places.id,places.name,places.host_id from places 
left join table1 on
places.host_id = table1.host_id
where table1.space_count>=2
order by places.id

상대적으로 간단한 문제

• 조회수가 가장 많은 중고거래 게시판

정답코드
# 1. USED_GOODS_BOARD , USED_GOODS_FILE JOIN 할것
# 2. 조회수가 가장 높은 게시물 체크
# 3. 해당 게시글 첨부파일 경로 조회
# 4. 첨부파일 경로는 FILE ID 기준 내림차순 정렬
# 5. 기본 파일 경로 = /home/grep/src/, 게시글 id 기준 디렉터리 구분
# 6. 파일 이름은 파일 id 파일 이름 파일 확정자로 출력
# 7. 조회 수가 가장 높은 게시물은 하나만 존재


# select * from used_goods_board as ugb
# left join used_goods_file as ugf on
# ugb.board_id = ugf.board_id

with table1 as(
    select ugb.board_id,max(ugb.views) as view from used_goods_board as ugb
    left join used_goods_file as ugf on
    ugb.board_id = ugf.board_id
    group by ugb.board_id
    order by max(ugb.views) desc
    limit 1
)
select concat('/home/grep/src/',ugf.board_id,'/',file_id,file_name,file_ext) as FILE_PATH
        from used_goods_file ugf
left join table1 on
ugf.board_id = table1.board_id
where table1.board_id is not NULL
order by ugf.file_id desc

0. 제공된 기본 테이블 2개 join
1. join한 테이블을 기반으로 최대 조회수를 알려주는 컬럼과 board_id로 구성된 테이블을 추가한다.
2. 만든 테이블과 파일 경로를 알려주는 테이블을 board_id를 기준으로 조인한다
3. join한 테이블에서 concat을 이용해서 쿼리를 조건에 맞게 출력한다.

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

# # 1. CAR_RENTAL_COMPANY_RENTAL_HISTORY 단일 테이블 사용
# # 2. 2022-08 ~ 2022-10까지 대여 횟수 5회 이상인 자동차 체크
# # 3. 해당 자동차 해당 기간 월 별 자동차 ID 별 대여횟수 컬럼 = RECORDS
# # 4. 월 기준으로 오름차순, 같을시 자동차 ID기준 내림차순
# # 5. 특정 월 대여 횟수 0인 경우는 제외
with table3 as (
    select car_id,  count(*) as month_count from car_rental_company_rental_history as crp
    where start_date like '2022-08%' or start_date like '2022-09%' or start_date like '2022-10%'
    group by car_id
    order by car_id
    # 기간 동안 5회이상 대여하지 않은 자동차를 거르기 위한 테이블
)
,table0 as (
    select car_id, month(start_date) as month , count(*) as month_count from car_rental_company_rental_history as crp
    where start_date like '2022-08%' or start_date like '2022-09%' or start_date like '2022-10%'
    group by car_id, month(start_date)
    order by car_id
), table1 as(
select MONTH, CAR_ID, sum(month_count) as RECORDS from table0
group by month, car_id
order by car_id
# order by month asc, car_id desc
        # 월과 차를 기준으로 그룹화 한 테이블
)
select table1.MONTH,table1.CAR_ID,table1.RECORDS from table1 
left join table3 on
table3.car_id = table1.car_id
where table3.month_count>=5
order by month asc, car_id desc
# 최종 결과를 나타내는 테이블