문제

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

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

(링크)

풀이

2021년에 가입한 전체 회원수

# 2021년에 가입한 전체 회원수
SELECT
    count(distinct user_id) as 'tot_cnt'
FROM
    user_info
WHERE
    year(joined) = 2021

추후 위 쿼리를 WITH문으로 사용할 예정이다 (서브쿼리도 가능은 하다)

2021년 가입 회원 중 구매 기록이 있는 회원수

SELECT
    o.user_id
    , year(o.sales_date) as 'sales_year'
    , month(o.sales_date) as 'sales_month'
    , count(distinct o.user_id) as 'sales_cnt'
FROM
    user_info u
    LEFT JOIN online_sale o
        ON u.user_id = o.user_id
WHERE
    year(u.joined) = 2021
    and (o.user_id) IS NOT NULL
GROUP BY
    o.user_id
    , sales_year
    , sales_month

여기서 주의해야 할 점이 있다
바로 sales_cnt에 반드시 distinct를 사용해주어야 한다는 점이다

distinct가 필요한 이유

해당 쿼리를 가공하기 전의 로데이터 형태로 살펴보자

SELECT
        year(o.sales_date) as 'sales_year'
        , month(o.sales_date) as 'sales_month'
        , o.user_id
    FROM
        user_info u
        LEFT JOIN online_sale o
            ON u.user_id = o.user_id
    WHERE
        year(u.joined) = 2021
        and (o.user_id) IS NOT NULL

그럼 아래와 같은 부분을 발견할 수 있다

이 캡쳐는 한 user_id가 동일 month에 물건을 여러개 구매하는 경우를 보여준다
만약 distinct를 사용하지 않으면 컴퓨터는 이를 2명으로 count한다

문제는 우리는 그 달에 구매한 회원수를 알고 싶은 것이기에, 이러한 중복값을 제거해주어야 하는 것!

최종 코드

WITH joined_users AS (
    # 2021년에 가입한 전체 회원수
    SELECT
        count(distinct user_id) as 'tot_cnt'
    FROM
        user_info
    WHERE
        year(joined) = 2021
),
purchased_users AS (
    SELECT
        year(o.sales_date) as 'sales_year'
        , month(o.sales_date) as 'sales_month'
        , count(distinct o.user_id) as 'sales_cnt'
    FROM
        user_info u
        LEFT JOIN online_sale o
            ON u.user_id = o.user_id
    WHERE
        year(u.joined) = 2021
        and (o.user_id) IS NOT NULL
    GROUP BY
        sales_year
        , sales_month
)
SELECT
    p.sales_year as 'year'
    , p.sales_month as 'month'
    , p.sales_cnt as 'purchased_users'
    , round(p.sales_cnt / j.tot_cnt, 1) as 'purchased_ratio'
FROM
    joined_users j
    , purchased_users p
ORDER BY
    year
    , month