[데이터분석 ] 코트카타 (SQL) - DATEDIFF (Lv.4 - Lv.5 진입)

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

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

SELECT car_id,
               case when car_id in 
              (select car_id
              from car_rental_company_rental_history
              where '2022-10-16' between start_date and end_date) 
              then '대여중'
              else '대여 가능' end 'AVAILABILITY'
from car_rental_company_rental_history
group by 1
order by 1 desc

 

 

60. 년, 월, 성별 별 상품 구매 회원 수 구하기

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

 

(상품을 구매한 회원수 집계 - 중복 없애기)

SELECT Year(sales_date) 'YEAR',
       month(sales_date) 'Month',
       gender,
       count(distinct b.user_id) 'Users'
from user_info a inner join online_sale b on a.user_id = b.user_id
where gender is not null
group by 1,2,3
order by 1,2,3 

 

 

61. 서울에 위치한 식당 목록 출력하기

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

 

(오답)

SELECT a.rest_id, a.rest_name, a.food_type, a.favorites, a.address,
       round(avg(b.review_score),2) as "리뷰_평균점수"
from rest_info a join rest_review b on a.rest_id = b.rest_id
where a.address like "서울%"
group by 1,2,3,4,5
order by 5 desc, 4 desc

 

 

(정답)

SELECT a.rest_id, a.rest_name, a.food_type, a.favorites, a.address,
       round(avg(b.review_score),2) as "리뷰_평균점수"
from rest_info a join rest_review b on a.rest_id = b.rest_id
where a.address like "서울%"
group by 1,2,3,4,5
order by 6 desc, 4 desc

 

(having 활용)

SELECT a.rest_id, b.rest_name, b.food_type, b.favorites, b.address,
       round(avg(a.review_score),2) as score
from rest_review a join rest_info b on a.rest_id = b.rest_id
group by 1
having b.address like '서울%'
order by 6 desc, 4 desc

 

(where절 활용)

SELECT a.rest_id, b.rest_name, b.food_type, b.favorites, b.address,
               round(avg(a.review_score),2) as score
from rest_review a join rest_info b on a.rest_id = b.rest_id
where b.address like '서울%'
group by 1
order by 6 desc, 4 desc

 

 

62. 자동차 대여 기록에서 장기/단기대여 구분하기

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

(오답)

SELECT history_id,
       car_id,
       start_date,
       end_date,
       case when history_id in 
       select history_id from car_rental_company_rental_history 
       where start_date like '2022-09%' between start_date and end >= '30' then '장기 대여' else '단기 대여' end 'rent_type'
from car_rental_company_rental_history
group by 1,2,3,4

 

(정답)

SELECT HISTORY_ID, CAR_ID, 
               DATE_FORMAT (START_DATE, "%Y-%m-%d") AS START_DATE, 
               DATE_FORMAT (END_DATE, "%Y-%m-%d") AS END_DATE,
CASE WHEN DATEDIFF(END_DATE, START_DATE) < 29 then '단기 대여' 
            ELSE '장기 대여' 
            END AS  RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC;

 

 

 

 

** 날짜, 시간차이 구하기  (DATE DIFF) 

MS-SQL에서 두개의 날짜사이의 차이값을 구해보자.

DATEDIFF() 함수는 두개의 날짜값의 차이를 int로 반환하는 Mssql 내장함수이다.

단순히 날짜의 차이가 아닌 두 날짜값의 년도 차이나 시간 차이 혹은 몇주가 차이나는지도 확인할수 있다.

 

[DATEDIFF() 구문]

SELECT  DATEDIFF('구분자','Start_Date','End_Date')

 

DATEDIFF()는 총 3개의 인수가 있는데 Start_Date와 End_Date는 차이를 구할 두개의 날짜값을 넣는곳이고

'구분자'는 어떤차이를 구할지 정해주는 부분이다.

예를 들어 두 날짜사이의 날짜 차이를 구하고 싶으면 'day' 혹은 'dd'등을 넣어주면 된다.

 

예제) 2018년의 날짜구하기

SELECT  DATEDIFF(dd,'2018-01-01','2018-12-31') + 1

결과 : 365

위와같이 구분값을 dd (day 며칠)로 넣어주면 된다.

※자주하는 실수인데 한달 일수를 구하는데 31일 - 1일을 하면 30일이 된다.

** 렌트 기간을 구할 떄 렌트를 시작한 날을 포함하기 때문 두기간의 차이값에서 +1을 해줘야 합니다.

 

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

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

 

(오답.. 답 나온거 같은데 왜지..?)

 

select car_id,
           round(avg(datediff(end_date, start_date)),1) as 'AVERAGE_DURATION'
from
(
SELECT car_id,
       date_format(start_date, "%Y-%m-%d") as start_date,
       date_format(end_date, "%Y-%m-%d") as end_date
from car_rental_company_rental_history) a
group by 1
having average_duration >= 7
order by 2 desc, 1 desc

 

(정답)

select car_id,
          round(avg(datediff(end_date, start_date)+1),1) as 'AVERAGE_DURATION'
from car_rental_company_rental_history
group by 1
having average_duration >= 7
order by 2 desc, 1 desc

 

** datediff 에서 +1 하는 이유!!

렌트기간을 구할 때 렌트를 시작한 날을 포함하기 때문에  두 기간의 차이값에서 +1을 해줘야함

ex) 2022-09-10일 부터 2022-09-15일까지 대여 기간을 표기해라

Dateiff( '2022-09-15', '2022-09-10') + 1 -> 총 대여일 (5+ 1)

 

 

64. 해비 유저가 소유한 장소 2021 Dev_Matching: 웹 백엔드개발자 (상반기) 

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

 

(오답)

SELECT ID,
       name,
       host_id
from places
group by 3
having count(host_ID)>1
order by 1

 

(정답)

select *
from places
where host_id in 

(
SELECT host_id  from places
group by host_id
having count(ID) >= 2
)
order by ID

 

---> 헤비 유저에 해당하는 유저들 먼저 찾고 (하늘색)

In 으로 씌우고 host_id 조건 줘버림

 

 

 

65. 우유와 요거트가 담긴 장바구니 Summer/Winter Coding (2019)66. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

SELECT cart_id
from cart_products
where name in ('Milk', 'Yogurt')
group by cart_id
having count(distinct name)=2    -> cart_id 로 묶고, name이 2개 있는 것만 (distinct로 중복 들어간 milk 나 Yogurt 제외)