hi-yujiniya

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/157342

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

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

풀이 과정_최초

1. 문제 요구 조건 확인
   자동차별 평균 대여 기간 반환 필요
   자동차별 평균 대여 기간 = 각 HISTORY_ID, CAR_ID별로 (END_DATE - START_DATE)를 구한 뒤,CAR_ID별로 HISTORY_ID 개수만큼 나눠주면 됨
   
   
2. 테이블 결합 조건 설정
   평균 대여 기간 구할 테이블 있어야 할 듯
   
   
3. 필터링 조건 설정
   평균 대여 기간 >=7
   
   
4. 필요 칼럼
   - CAR_ID
   - AVERAGE_DURATION
   
   
5. 최종 뷰 조건
   - 평균 대여 기간 (AVERAGE_DURATION)으로 DESC, CAR_ID로 ASC

A. 초기 답안

with period as (
select HISTORY_ID
    , CAR_ID
    , (END_DATE - START_DATE) as rent_period
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by HISTORY_ID)
select CAR_ID
, round(sum(rent_period)/count(distinct HISTORY_ID),2) as AVERAGE_DURATION
from period
group by CAR_ID
order by AVERAGE_DURATION desc, CAR_ID asc

• 가독성: 굳이 CTE를 쓰지 않아도 되는데, 쓰는 바람에 가독성 떨어짐 → AVG로 계산이 더 명확
• 성능: CTE + SUM + COUNT + GROUP BY = 중간 계산 많음
• 7일 이상인 필터링 조건도 적용 안했고
• "대여"의 개념이 시작일이 포함되어야 하므로, 날짜끼리 뺀거에서 1을 더해줘야 함

A2. 최종 답안

select
CAR_ID
, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by CAR_ID
having AVERAGE_DURATION>=7
order by AVERAGE_DURATION desc, CAR_ID desc

• 가독성: AVG 함수 활용하여 가독성 명확
• 성능: CTE가 없어 성능 향상

짚고 넘어갈 것(1)- 날짜 집계 관련

• 1. DATEDIFF(col1,col2)
  두 값의 차이를 INT로 반환

#활용 예시 

#1. 평균 체류일
AVG(DATEDIFF(end,str))

#2. 7일 이상 대여
where DATEDIFF(end,str)>=7

#3. 최근 이벤트까지의 날짜 차이
DATEDIFF(CURDATE(),event_date)

• TIMESTAMPDIFF(col1, col2)
• DATE_ADD(), DATE_SUB()

짚고 넘어갈 것(2)- Having

그냥 왜 그런지 모르겠으나...having을 잘 안써버릇했는데.....익혀놔야겠다

where과 having 차이

• where 
  • 필터링 대상: 개별 행 기준으로 필터링
  • 위치: group by 전 
  • ex: where price >=1000
• having
  • 필터링 대상: 그룹 집계 결과
  • 위치: group by 후
  • ex: having AVG(price)>1000

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/59405

Q. 가격이 제일 비싼 식품의 정보 출력하기

FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

풀이 과정

굉장히 간단한 문제이긴한데..다른 방식으로 푸는 케이스가 많을 것 같아서 기록

1. 문제 요구 조건 확인
   가격이 제일 비싼 식품에 대한 값만 반환하면 됨
   
   
2. 테이블 결합 조건 설정
   가장 비싼 식품에 대한 정보만 불러오면 된다고 했지만, 공동 1위가 있을 수도 있어서 rank만 뽑는 CTE 생성
   
   
3. 필터링 조건 설정
   "가장 비싼" = rank CTE에서 rank:1
   
   
4. 필요 칼럼
   - 전체
   
   
5. 최종 뷰 조건
   - 1마리 이름만 나오면 됨 = limit 1

A. 최종 답안

WITH price_rank as (
select
    PRODUCT_ID
    ,rank() over(order by PRICE	 desc) as price_rk
from FOOD_PRODUCT)
select 
f.PRODUCT_ID
, f.PRODUCT_NAME
, f.PRODUCT_CD
, f.CATEGORY
, f.PRICE
from FOOD_PRODUCT f
left join price_rank p
on f.PRODUCT_ID=p.PRODUCT_ID
where p.price_rk=1

• 가독성: JOIN이 필요 없을 수도 있어 살짝 개선 여지 있음
• 성능: RANK()는 윈도우 전체 계산 → 데이터 양 많아지면 비용 발생
• 확장성: PARTITION, TOP N 확장 시 매우 유리

▼피드백 반영한 다른 버전

1. join 없이 CTE에서 모든 칼럼 붙이기

WITH price_rank as (
select
    PRODUCT_ID
    ,PRODUCT_NAME
    ,CATEGORY
    ,PRICE
    ,rank() over(order by PRICE	 desc) as price_rk
from FOOD_PRODUCT)
select PRODUCT_ID
    ,PRODUCT_NAME
    ,CATEGORY
    ,PRICE 
from price_rank
where price_rk=1

• 가독성: join 없어서 전보다 나음
• 성능: 기존안보다 조금 더 개선되거나 동일
• 확장성: 기존의 우수한 확장성은 유지

2. where절에 MAX로 조건 추가

select
    PRODUCT_ID
    ,PRODUCT_NAME
    ,CATEGORY
    ,PRICE
from FOOD_PRODUCT
where PRICE = (select max(PRICE) from FOOD_PRODUCT)

• 가독성: 가장 단순한 방법임
• 성능: 단순해서 좋음
• 확장성: rank 버전보다 떨어짐
• ✨가장 비싼 제품 1개만 구한다면 → MAX가 유리, 가장 비싼 제품들을 구하라고 하면 → rank가 유리

짚고 넘어갈 것  -  RANK() vs DENSE_RANK() vs ROW_NUMBER()

나올때마다 한 번 씩 더 정리할 것...

1. RANK 구조
rank는 동순위가 있으면 다음 순위 건너뜀 (1, 1, 3, 4,,,)

#partition 조건 없는 경우
rank() over(order by Col)

#partition 조건 있는 경우
rank() over(partition by col1 order by col2)
###col1 그룹 내에서 col2 기준 asc로 Rank 부여

2. DENSE RANK
dense rank는 동순위가 있어도 건너뛰지 않음(1,1,2,3,,,)

#partition 조건 없는 경우
dense_rank() over(order by Col)

#partition 조건 있는 경우
dense_rank() over(partition by col1 order by col2)
###col1 그룹 내에서 col2 기준 asc로 Rank 부여

3. ROW_NUMBER()
order by한 뒤 고유 번호 부여 (동순위 여부에 관계없이 그냥 순서대로 1,2,3,4,,,,)

#partition 조건 없는 경우
row_number() over(order by Col)

#partition 조건 있는 경우
row_number() over(partition by col1 order by col2)

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/59405

Q. 상위 n개 레코드

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

풀이 과정

굉장히 간단한 문제이긴한데..다른 방식으로 푸는 케이스가 많을 것 같아서 기록

1. 문제 요구 조건 확인
   가장 먼저 들어온 동물 이름을 반환하면 됨
   
   
2. 테이블 결합 조건 설정
   단일 테이블
   
   
3. 필터링 조건 설정
   "가장 먼저 들어온" = min(DATETIME) 
   
   
4. 필요 칼럼
   - DATETIME
   -NAME
5. 최종 뷰 조건
   - 1마리 이름만 나오면 됨 = limit 1

A. 최종 답안

select NAME
from ANIMAL_INS
order by DATETIME asc 
limit 1

• 가독성: 직관적임
• 성능: 최적
• 확장성: 낮음 - 오직 상위 1개만 한정

1. Rank 적용한 CTE 만들기

# rank - CTE 생성하기

with rk as (
select NAME
, rank() over(order by DATETIME asc) as dt_rank
from ANIMAL_INS
)
select NAME
from rk
where dt_rank=1

• 가독성: 명확히 rank=1을 명시해주므로 좋음
• 성능: 테이블 전체를 조회해서 rank를 부여하므로 limit 1보다는 성능이 떨어짐
• 확장성: 다른 partition 조건을 걸거나, 다른 rank를 확인하는 등 확장 측면에서는 limit보다 우수

# min(DATETIME)으로 조건 걸기

select NAME
from ANIMAL_INS
where DATETIME = (select min(DATETIME) from ANIMAL_INS)

• 가독성: 명시적으로 min(DATETIME)을 보여줘서 직관적임
• 성능: DATETIME이 같은 동물이 있을 경우 여러 마리 반환
• 확장성: CTE 생성 버전보다는 떨어지는 것 같으나, limit 1보다는 확장성 있음

짚고 넘어갈 것  -  Rank

구문이 항상 헷갈림....

1. RANK 구조
rank는 동순위가 있으면 다음 순위 건너뜀 (1, 1, 3, 4,,,)

#partition 조건 없는 경우
rank() over(order by Col)

#partition 조건 있는 경우
rank() over(partition by col1 order by col2)
###col1 그룹 내에서 col2 기준 asc로 Rank 부여

2. +) 개념으로, DENSE RANK
dense rank는 동순위가 있어도 건너뛰지 않음(1,1,2,3,,,)

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/131536

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

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

풀이 과정

1. 문제 요구 조건 확인
   한 상품(PRODUCT_ID)을 구입한 이력이 2회 이상인 경우 집계
   
   
2. 테이블 결합 조건 설정
   없음
   
   
3. 필터링 조건 설정
   없음
   
   
4. 필요 칼럼
   - USER_ID
   - PRODUCT_ID
5. 최종 뷰 조건
   - USER_ID - ASC
   - PRODUCT_ID - DESC

A. 최종 답안

with order_cnt as (
select USER_ID
    , PRODUCT_ID
    , count(distinct SALES_DATE) as order_dt_cnt
from ONLINE_SALE
group by USER_ID, PRODUCT_ID)
select USER_ID,
PRODUCT_ID
from order_cnt
where order_dt_cnt>=2
order by USER_ID asc, PRODUCT_ID desc

짚고 넘어갈 것

1. DISTINCT vs ALL (집계 연산)
Count(DISTINCT)는 중복 제거하기 때문에 정확한 재구매 판단에는 적합.
다만 이번 케이스는 "동일 날짜, 회원 ID, 상품 ID에 대해서는 하나의 데이터만 존재한다"고 했음. 즉, 굳이 distinct 처리하지 않아도 동작함.

평가 (by gpt)

1. 가독성
WITH절 사용으로 쿼리 구조가 명확하게 분리됨. 별칭도 직관적 (order_cnt)

2. 성능
COUNT(DISTINCT SALES_DATE)는 정확하지만 인덱스를 사용하기 어려움 (집계 연산은 무조건 테이블 스캔 또는 인덱스 스캔 필요). COUNT(*)보다 살짝 무거움

3. 확장성
CTE(WITH) 구조로 되어 있어서 나중에 JOIN, 필터 추가, 집계 확장 등 쉽게 가능

문제 출처: 프로그래머스
https://school.programmers.co.kr/learn/courses/30/lessons/164673

Q. 조건에 부합하는 중고거래 댓글 조회하기 

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

풀이 과정

1. 문제 요구 조건 확인
   2022년 10월에 작성된 게시물과 관련된 value를 USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 가져와야 함
   
   
2. 테이블 결합 조건 설정
   댓글이 있는 데이터만 조회하면 되므로, Join으로 결합
   
   
3. 필터링 조건 설정
   -날짜: 2022년 10월 데이터만 조회하면 됨
   
   
4. 필요 칼럼
   - USED_GOODS_BOARD 테이블: TITLE, BOARD_ID
   - USED_GOODS_REPLY 테이블: REPLY_ID, WRITER_ID, CONTENTS, CREATED_DATE
5. 최종 뷰 조건
   - 날짜: yyyy-mm-dd 형태로 나와야함
   - Order by: 댓글 작성일 ASC, 게시물 제목 ASC

A. 최종 답안

select 
B.TITLE
, B.BOARD_ID
, R.REPLY_ID
, R.WRITER_ID
, R.CONTENTS
, date_format(R.CREATED_DATE, '%Y-%m-%d') as created_date
from USED_GOODS_BOARD B
JOIN USED_GOODS_REPLY R
ON B.BOARD_ID=R.BOARD_ID
WHERE B.CREATED_DATE BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY R.CREATED_DATE asc , B.TITLE asc

짚고 넘어갈 것

1. 날짜 관련 함수

#1. 년도/월만 따로 떼어서 보기
WHERE YEAR(B.CREATED_DATE)=2022 AND MONTH(B.CREATED_DATE)=10

#2. 년-월로 필터링
WHERE DATE_FORMAT(B.CREATED_DATE,'%y-%m)='2022-10'

2. 인덱스를 타게 만드는 조건이 성능이 좋음
where 조건에 함수를 쓰면 인덱스를 못 타기 때문에 상대적으로 효율 떨어짐 → 되도록 칼럼 그대로 써서 필터링 하는 게 좋음 

평가 (by gpt)

1. 가독성
명확한 테이블 alias (B, R), 정렬 기준도 이해하기 쉬움

2. 성능
인덱스를 타는 필터, INNER JOIN, 컬럼 추출 최소화

3. 확장성
JOIN이 간단하여 향후 댓글 개수 집계 등 추가 가능 (예: COUNT(*) OVER 등)