코드카타 Level.5 76

76.  상품을 구매한 회원 비율 구하기 

문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/131534

정답 코드:

SELECT
    YEAR(os.SALES_DATE) AS YEAR,
    MONTH(os.SALES_DATE) AS MONTH,
    COUNT(DISTINCT os.USER_ID) AS PURCHASED_USERS,
    # 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
    ROUND(
        COUNT(DISTINCT os.USER_ID) /
        (
        SELECT COUNT(*)
        FROM USER_INFO
        WHERE JOINED >= '2021-01-01'
        AND JOINED < '2022-01-01')
        , 1) AS PURCHASED_RATIO
FROM USER_INFO ui
JOIN ONLINE_SALE os
    ON os.USER_ID = ui.USER_ID
WHERE ui.JOINED BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY YEAR(os.SALES_DATE), MONTH(os.SALES_DATE)
ORDER BY YEAR ASC, MONTH ASC;

 

문제 풀이때 했던 생각.

# 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO # 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE # 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율 # (=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수) # 년, 월 별로 출력 # 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림 # 전체 결과는 년을 기준으로 오름차순 정렬 #  년이 같다면 월을 기준으로 오름차순 정렬

 

---