[JOIN] 상품을 구매한 회원 비율 구하기

문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

💡 조건

• user_info 테이블에서 21년도에 가입한 사람 추출
• online_sale 테이블에서 연도, 월별 구매자 수
• 월별 구매자/21년도 가입자 = ratio (반올림 있음)
• 년을 기준으로 오름차순, 월을 기준으로 오름차순

sql 코드

SELECT year(sales_date) as year, month(sales_date) as month,
        count(distinct(online.user_id)) as purchased_users,
        ROUND(count(distinct(online.user_id))/
              (SELECT count(user_id)
                FROM user_info
                WHERE year(joined)=2021),1) as purchase_ratio
FROM online_sale as online join
        (SELECT user_id
        FROM user_info
        WHERE year(joined)=2021) as user
        on online.user_id = user.user_id
GROUP BY year(sales_date), month(sales_date)
ORDER BY year, month

조금 지저분하게 해결한 것 같지만 해결한 과정을 차근차근 적어둔다.

1. 21년도에 가입한 사람 뽑아서 online 테이블과 합쳐주기

FROM online_sale as online join
        (SELECT user_id
        FROM user_info
        WHERE year(joined)=2021) as user
        on online.user_id = user.user_id

문제의 조건은 21년도에 가입한 사람 중에서 월별 구매 비율을 구하는것. 그러니 21년도의 user_id만 추출해서 online 테이블의 user_id와 합쳐주었다.

2. 연도와 월로 그룹화

GROUP BY year(sales_date), month(sales_date)

연도와 월별로 구매자 수를 출력해줘야하기 때문에 일단 그룹화해준다. 연도로 1차, 월로 2차.

3. 출력해서 보여줄게 무엇인가?

SELECT year(sales_date) as year, month(sales_date) as month,
        count(distinct(online.user_id)) as purchased_users,
        ROUND(count(distinct(online.user_id))/
              (SELECT count(user_id)
                FROM user_info
                WHERE year(joined)=2021),1) as purchase_ratio

1. year와 month 출력 (alias 사용)

   SELECT year(sales_date) as year, month(sales_date) as month

2. 월별 구매자 수 출력
   🔎 user_id는 고유하지만, 구매이력은 user가 한개 이상을 구매할 수 있다. 구매이력이 2번이라고 해서 구매자가 2명인 것이 아니므로 구매자 수를 구할 때는 DISTINCT를 사용해서 중복을 제거해주었다.

   count(distinct(online.user_id)) as purchased_users

3. 구매 비율 구하기
   🔎 비율을 구할 때 분모로 들어가는게 21년도 가입자 수이므로, 해당 테이블에서 바로 user_id를 count해주면 월별로 구해지기 때문에 답이 달라진다. 따라서 서브쿼리를 사용해서 21년도의 전체 가입자 수를 분모로 넣어주었다. 소수 둘째자리에서 반올림이 있으므로 ROUND함수를 사용했다.

   ROUND(count(distinct(online.user_id))/
               (SELECT count(user_id)
                 FROM user_info
                 WHERE year(joined)=2021),1) as purchase_ratio

4. 년을 기준으로 오름차순, 월을 기준으로 오름차순

   ORDER BY year, month