SQL | 상품을 구매한 회원 비율 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

---

처음 쓴 답 

-- 2021년 가입
-- 상품을 구매한 회원수, 상품을 구매한 회원 비율(소수점 두번째 자리 반올림)
-- 년, 월별로 출력
-- 년 기준 오름차순, 월 기준 오름차순
SELECT
    YEAR(o.SALES_DATE) AS YEAR,
    MONTH(o.SALES_DATE) AS MONTH,
    COUNT(DISTINCT u.USER_ID) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT u.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE '2021%'),1) AS PUCHASED_RATIO
FROM
    USER_INFO u LEFT JOIN ONLINE_SALE o ON u.USER_ID = o.USER_ID
WHERE u.JOINED LIKE '2021%' AND o.SALES_DATE LIKE '2022%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH ;

📍 AND o.SALES_DATE LIKE '2022%' 이 부분 안 써서 오답 나왔었음

with 활용

WITH a AS (
    SELECT
        *
    FROM
        USER_INFO
    WHERE JOINED LIKE '2021%')
SELECT
    YEAR(o.SALES_DATE) YEAR,
    MONTH(o.SALES_DATE) MONTH,
    COUNT(DISTINCT u.USER_ID) PURCHASED_USERS,
    ROUND(COUNT(DISTINCT u.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) FROM a),1) PUCHASED_RATIO
FROM
    USER_INFO u LEFT JOIN ONLINE_SALE o ON u.USER_ID = o.USER_ID
WHERE 
    u.JOINED LIKE '2021%' AND o.SALES_DATE LIKE '2022%'
GROUP BY 1, 2
ORDER BY 1, 2 ;

💡문제를 제발 꼼꼼하게 읽자!

💡 중간중간 코드 돌아가는지 확인도!