[Algorithm_SQL] 자동차 평균 대여 기간 구하기 (Success)

프로그래머스

🙂 확인 사항

1. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

2. 평균 대여 기간이 7일 이상

3. 자동차 ID, 평균 대여기간

4. 평균 대여 기간 컬럼명: AVERAGE_DURATION

5. 평균 대여 기간: 소수점 두번째 자리에서 반올림

6. 평균 대여 기간 내림차순, 자동차 ID 내림차순 정렬

 

 

📝 Language: Oracle

  SELECT CAR_ID, 
         ROUND(AVG((END_DATE-START_DATE+1)),1) AVERAGE_DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
  HAVING ROUND(AVG((END_DATE-START_DATE+1)),1)>=7
ORDER BY ROUND(AVG((END_DATE-START_DATE+1)),1) DESC, 
         CAR_ID DESC
;
 

GROUPG BY + HAVING

 

WITH AVGTABLE AS(
      SELECT CAR_ID,
             ROUND(AVG((END_DATE-START_DATE+1)),1) AVERAGE_DURATION
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID
)
  SELECT CAR_ID, 
         AVERAGE_DURATION
    FROM AVGTABLE
   WHERE AVERAGE_DURATION>=7
ORDER BY AVERAGE_DURATION DESC, 
         CAR_ID DESC
;
 

공통 표현식

 

  SELECT CAR_ID, 
         AVERAGE_DURATION
    FROM (
        SELECT CAR_ID,
               ROUND(AVG(END_DATE - START_DATE + 1), 1) AS AVERAGE_DURATION
          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      GROUP BY CAR_ID
    ) 
   WHERE AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, 
         CAR_ID DESC
;
 

인라인 뷰

 

 

 

🔗 소스 코드
GitHub