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

• 중복 제거하기

SELECT count(DISTINCT name) from animal_ins

• 동물 수 구하기

SELECT count(*) from animal_ins

• 최솟값 구하기

SELECT datetime from animal_ins
order by datetime asc
limit 1

• 동명 동물 수 찾기

with table1 as (
SELECT NAME, count(name) as COUNT from animal_ins
where name is not NULL
group by name 
)
select * from table1
where table1.count >=2
order by table1.name

• 이름에 el 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'Dog' 
ORDER BY UPPER(NAME)

// UPPER은 문자열을 대문자로 바꿔주는 함수
강아지를 찾는걸 못보고 삽질하다 대소문자 구분 없이 찾는 다는걸 보고 대문자로 만들면 되겠구나 싶어서 구글링해서 사용했는데 안됬던 원인은 강아지를 찾는 코드를 추가 안한것...

결론
문제를 잘 읽어야한다!!!

• NULL 처리하기

SELECT ANIMAL_TYPE, 
(
    CASE WHEN NAME IS NULL THEN 'No name'
    else NAME END
) AS NAME
, SEX_UPON_INTAKE FROM ANIMAL_INS

• DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, SUBSTRING(DATETIME,1,10) AS '날짜' FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• 가격이 제일 비싼 식품의 정부 출력하기

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE FROM FOOD_PRODUCT
WHERE PRICE = (
    SELECT MAX(PRICE) FROM FOOD_PRODUCT
)

// 처음 MAX를 이용해서 PRICE = MAX(PRICE)를 기입하니 문법 오류 발생해서
SUB QUERY를 사용하니 문제가 해결됬다.
MAX나 MIN, 등등은 SUB QUERY를 사용해야 된다!!

• 고양이와 개는 몇마리 있을까?

SELECT ANIMAL_TYPE,COUNT(*) FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

처음 이 문제 풀때 정렬하더라도 고양이가 먼저 조회되길래 그냥 제출 후 채점하였더니 역시나
실패... ORDER BY 추가하니 정답으로 나왔다...
출력 결과는 차이가 없어도 문제가 요구한 내용을 코드로 다 구현해야한다. 실무에서도 마찬가지 일 것이다.

• 중성화 여부 파악하기
  Sol1

SELECT ANIMAL_ID, NAME,
(
    CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%'  OR 
    SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
    ELSE 'X' END
) as 중성화
FROM ANIMAL_INS

이 문제에서 약간 해맸다.
like를 CASE에 사용해본 경험이 별로 없었기 때문이다.
구글링 결과 like를 사용하는 방법을 알 수 있었다
// https://yaddooooooong.tistory.com/17

Sol2

SELECT ANIMAL_ID, NAME,
(
    CASE WHEN LOCATE('Neutered',SEX_UPON_INTAKE)  OR 
    LOCATE('Spayed',SEX_UPON_INTAKE) THEN 'O'
    ELSE 'X' END
) as 중성화
FROM ANIMAL_INS

그러다 문득 like 말고 다른 방법은 없을까? 라는 생각이 들었고 구글링 결과 locate() 함수에 대해서도 알게 되었다.
// https://codingspooning.tistory.com/entry/MySQL-%EB%AC%B8%EC%9E%90%EC%97%B4-%ED%95%A8%EC%88%98

• 입양 시각 구하기

SELECT substring(datetime,12,2), count(*) from animal_outs
where substring(datetime,12,2) between 9 and 19
group by substring(datetime,12,2)
order by substring(datetime,12,2)

이 문제는 생각보다 간단 했다.
substring을 이용해서 시간 부분만 분리해서 사용하는게 핵심이다.

• 카테고리별 상품 개수 구하기

SELECT substring(product_code,1,2) as PRODUCT_ID,count(*) AS PRODUCTS from product
group by substring(product_code,1,2)
order by substring(product_code,1,2) asc

이 문제도 방금 위에 문제와 거의 유사한 문제이다.

• 진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD,COUNT(*) AS '5월예약건수' FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD 
ORDER BY COUNT(*) ASC, MCDP_CD ASC

• 루시와 에라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME IN ('lucy','Ella','Pickle','Rogan','Sabrina','Mitty')

이 문제의 핵심은 in을 사용할 줄 아는지 여부이다!!!

• 상품 별 오프라인 매출 구하기

select PRODUCT_CODE, PRICE*SUM(SALES_AMOUNT) AS SALES  from offline_sale
left join product on offline_sale.product_id = product.product_id
GROUP BY PRODUCT_CODE
ORDER BY PRICE*SUM(SALES_AMOUNT) DESC,  PRODUCT_CODE ASC

삽질... COUNT가 아니라 SUM을 사용해야한다...
거의 30분 동안 SALES_AMOUNT를 생각하지 않고 COUNT만 사용하며 왜 안되지???
이것 만 반복했다.
이런 사태의 원인은 SQL쿼리를 차근 차근 체크하지 않았기 때문이다.
DB가 돌아 가는 흐름을 확실히 알아야 한다!!!

• 3월에 태어난 여성 회원 목록 출력하기 x

SELECT MEMBER_ID,MEMBER_NAME, GENDER, SUBSTRING(DATE_OF_BIRTH,1,10) AS DATE_OF_BIRTH
        FROM MEMBER_PROFILE 
WHERE TLNO IS NOT NULL AND SUBSTRING(DATE_OF_BIRTH,6,2) LIKE '%03%'
ORDER BY MEMBER_ID ASC

생각보다 안풀린다...
뭐가 문제인지 모르겠다...
조건도 다 맞췄는데 왜 안풀리는건지....

• 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

// 실패 코드
SELECT CAR_TYPE,COUNT(CAR_TYPE) AS CARS FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR '%열선시트%'OR'%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

맨 처음 시도하였던 코드는 LIKE문에 OR을 사용해서 세가지 옵션을 받을려고 했다
하지만 생각과는 다르게 LIKE '%통풍시트%' 하나만을 체크하고 나머지 OR 문은 NULL이 아니니 TRUE로 판별하여 의미가 없어진다.
아래 그림을 보면 확실히 알 수 있다.

이를 해결하기 위해서는 기존의 코드 형태를 바꾸어야한다.
변경 코드

SELECT * FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%'OR OPTIONS LIKE'%가죽시트%'
ORDER BY CAR_TYPE

변경 코드는 3개의 옵션을 고려하는 형태로 변하게 된다.

최종 정답 코드

SELECT CAR_TYPE, COUNT(CAR_TYPE) AS CAR FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%'OR OPTIONS LIKE'%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

• 조건에 맞는 도서와 저자 리스트 출력하기

SELECT BOOK_ID,AUTHOR.AUTHOR_NAME,SUBSTRING(BOOK.PUBLISHED_DATE,1,10) FROM BOOK
LEFT JOIN AUTHOR ON 
BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
WHERE BOOK.CATEGORY = '경제'
ORDER BY BOOK.PUBLISHED_DATE ASC

무난했던 문제

• 성분으로 구분한 아이스크림 총 주문량

SELECT ingredient_type,sum(total_order) as TOTAL_ORDER from first_half
left join icecream_info on
first_half.flavor = icecream_info.flavor
group by ingredient_type

무난했던 문제 left join을 통해 수평으로 합치고 sum으로 group 별로 묶은 아이스크림에 대한 수량을 출력

• 가격대 별 상품 개수 구하기

1차 시도
with table1 as (
    SELECT (round(price /10000)*10000) as price_group ,product_id  from product
)
SELECT price_group as PRICE_GROUP, count(product_id) as PRODUCTS FROM table1
group by price_group
order by price_group asc

약간 고전했던 문제이다. ROUND라는 함수에 의해 데이터가 어떻게 변하는지 확실하게 알아야 풀 수 있는 문제이다
위 코드 라운드는 반올림을 한다. 즉 내림을 하는 다른 함수가 필요하다
구글링 결과 ROUNDDOWN이라는 함수를 알게 되었다
https://support.microsoft.com/ko-kr/office/round-%ED%95%A8%EC%88%98-c018c5d8-40fb-4053-90b1-b3e7f61a213c

예상과 다르게 ROUNDDOWN 함수는 SQL에 없었다.
그 대신 FLOOR 이라는 함수가 있다는 것을 알 수 있었다.
https://blog.gitnux.com/code/sql-round-down/

정답 코드

with table1 as (
    SELECT (FLOOR(price /10000)*10000) as price_group ,product_id  from product
)
SELECT price_group as PRICE_GROUP, count(product_id) as PRODUCTS FROM table1
group by price_group
order by price_group asc

이 문제는 솔루션을 생각하는 건 어렵지 않았지만
기존에 사용하는 함수에 대해 잘 파악하는 것이 중요하다는 것을 상기시킨다.

• 재구매가 일어난 상품과 회원 리스트 구하기
  

이 문제는 USER_ID로 그룹 화 한 이후 PRODUCT_ID가 중복인 것을 찾아내는 것이 핵심이라 생각된다.
어떻게 중복인 것을 알수 있을까?

group by 를 연이어 사용하면 가능하지 않을까?

USER_ID로 그룹을 만들고 그 안을 PRODUCT_ID로 그룹화해서 말이다.

정답 코드
WITH TABLE1 AS(
    SELECT USER_ID,PRODUCT_ID,COUNT(SALES_AMOUNT) AS AMOUNT FROM ONLINE_SALE
    GROUP BY USER_ID,PRODUCT_ID
    ORDER BY COUNT(SALES_AMOUNT) DESC
)
SELECT USER_ID,PRODUCT_ID FROM TABLE1
WHERE AMOUNT >=2
ORDER BY USER_ID ASC, PRODUCT_ID DESC

• 조건에 부합하는 중고거래 상태 조회하기

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 LIKE '2022-10-05%'
ORDER BY BOARD_ID DESC

CASE문을 잘 사용할 수 있는지가 요점인 문제이다.

• 자동차 평균 대여 기간 구하기
  조건 1. 평균 대여 기간이 7일 이상인 자동차
  조건 2. 해당 자동차들의 ID와 평균 대여 기간 출력
  조건 3. 소수점 2자리에서 반올림
  조건 4. 평균 대여기간 기준으로 내림차순, 같을시 자동차 ID 기준 내림차순

평균 대여기간
-> GROUP BY로 CAR_ID 기준 그룹화, SUM으로 대여기간 전부 합친 이후 COUNT(*)한 개수로 분할

문제 발생 -> 연도가 넘어가는 경우 잘못된 값 출력
단순 연산이 아닌 날짜를 계산하는 함수가 필요하다.
https://velog.io/@12aeun/SQL-mysql%EC%97%90%EC%84%9C-%EB%82%A0%EC%A7%9C-%EC%8B%9C%EA%B0%84-%EA%B3%84%EC%82%B0%ED%95%98%EA%B8%B0

datediff로 사이 일수 확보 당일을 포함하지 않기에 +1해준다

WITH table1 as (
    
SELECT CAR_ID, DATEDIFF(END_DATE,START_DATE)+1 AS RENTAL_DURATION
         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    
    
ORDER BY CAR_ID
), table2 as (
select CAR_ID,round(sum(rental_duration)/count(rental_duration),1) as AVERAGE_DURATION from table1
group by car_id
order by AVERAGE_DURATION DESC, car_id desc
)
select * from table2
where AVERAGE_DURATION >= 7
order by AVERAGE_DURATION DESC, car_id desc