[프로그래머스] 상품을 구매한 회원 비율 구하기 📌

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

프로그래머스

 

 

 

SELECT
    YEAR(s.sales_date) AS year,
    MONTH(s.sales_date) AS month,
    COUNT(DISTINCT s.user_id) AS purchased_users,
    ROUND(COUNT(DISTINCT s.user_id)/(
        SELECT COUNT(DISTINCT user_id)
        FROM user_info
        WHERE YEAR(joined) = 2021), 1) AS purchased_ratio
FROM user_info u
    LEFT JOIN online_sale s ON u.user_id = s.user_id
WHERE YEAR(u.joined) = 2021
GROUP BY year, month
HAVING year IS NOT NULL
ORDER BY year, month

 

소숫점 둘쨋자리에서 반올림 = 소숫점 첫째자리까지만 출력 원함 = ROUND 함수의 인수 1

 

https://chaemi720.tistory.com/239

[프로그래머스] 상품을 구매한 회원 비율 구하기 - MySQL

JOIN을 쓰지 않고도 풀 수 있었다 !

1. 2021년에 가입한 사람 수

SELECT COUNT(user_id)
FROM user_info
WHERE YEAR(joined) = 2021

 

2. 2021년에 가입한 사람 중 구매한 사람

COUNT(DISTINCT user_id)

 

SELECT YEAR(sales_date) AS year,
    MONTH(sales_date) AS month,
    COUNT(DISTINCT user_id) AS purchased_users,
    ROUND(COUNT(DISTINCT user_id)/(
        SELECT COUNT(user_id)
        FROM user_info
        WHERE YEAR(joined) = 2021), 1) AS purchased_ratio
FROM online_sale
WHERE user_id IN (
    SELECT user_id 
    FROM user_info 
    WHERE YEAR(joined) = 2021) -- 21년도 가입 유저 ID
GROUP BY year, month
ORDER BY year, month