[75] 부트캠프 TIL - 본캠프 56일차

1. 프로그래머스 사용

1-1)  sql 문제 : 상품을 구매한 회원 비율 구하기 
ㄴ 문제 : 의류쇼핑몰 회원정보 테이블과 온라인상품 판매정보 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의비율(계산식 아래 작성예정)을 년,월 별로 출력하는 sql문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째 자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고, 년도가 같다면 월을 기준으로 오름차순 정렬해주세요.

 

문제풀이)

1) 문제를 읽고 필요한 조건들을 체크한다
- 년,월,회원수, 회원비율을 구한다.
 
2) 회원 비율 구하기 계산식
- (2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원수 ) 

- 위 계산식으로 비율을 구해야하고 해당 비율은 ROUND함수를 사용해 소수점 두번째자리에서 반올림해야한다.
 
3) 2021년에 가입
-  회원정보 테이블에 JOINED를 기준으로 '2021' 적용해준다.
 
4) 년,월 별로 출력
-  GROUP BY를 년,월을 기준으로 적용해준다.
 
5) 년,월 오름차순
-  ORDER BY ASC를 활용해서 정렬한다.
 
6) 최종 완성 쿼리문
-

SELECT 
    YEAR(b.SALES_DATE) AS 'YEAR',
    MONTH(b.SALES_DATE) AS 'MONTH',
    COUNT(DISTINCT b.USER_ID) AS 'PURCHASED_USERS',
    ROUND(COUNT(DISTINCT b.USER_ID)/(SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE DATE_FORMAT(joined, '%Y') = '2021'), 1) AS PURCHASED_RATIO
FROM USER_INFO a LEFT JOIN ONLINE_SALE b ON a.USER_ID = b.USER_ID
WHERE DATE_FORMAT(a.joined, '%Y') = '2021'
AND YEAR(b.SALES_DATE) IS NOT NULL
GROUP BY 1,2
ORDER BY 1 ASC, 2 ASC

- 년,월은 구매한 회원수를 구해야하기때문에 ONLINE_SALE 테이블에 판매일(SALES_DATE)를 기준으로 YEAR,MONTH함수로 구한다.

- 회원의 수도 ONLINE_SALE 테이블의 회원ID로 중복제거후 구한다.

- 회원비율은 문제에서 제시했던 조건대로 계산하여 구해낸다. 판매회원수는 PURCHASED_USERS와 동일하고, 뒤에 2021년에 가입한 회원수는 USER_INFO테이블에서 2021년 조건을 넣어서 구해온다.

-  그리고 WHERE문으로 위에서 구한 회원수와 비율을 제대로 적용하기 위해서 2021년에 가입한 회원이라는 조건을 WHERE DATE_FORMAT(a.joined, '%Y') = '2021' 을 넣어서 적용시킨다.

- 판매년도가 비어있는 데이터가 발생하기 때문에 IS NOT NULL로 해당데이터는 제외시켜준다.

- 마지막으로 년,월을 기준으로 GROUP BY와 ORDER BY를 진행하여 정렬해준다.