[SQL] 코드카타 3주 (월)

양승우·2024년 10월 14일

코드카타

목록 보기
14/58

오늘부터 문제 번호 표기, 글 제목에 주차&요일 표기로 형식을 바꿔보고자 한다.

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

1) 2021년 가입
2) 상품 구매한 회원 수와 그 비율(소수점 둘째자리)
3) 년, 월별로 출력
4) 년 오름차순, 월 오름차순

일단 중간 테스트

SELECT
    *
FROM
    (SELECT
        user_id
        , product_id
    FROM
        ONLINE_SALE
    ) o
    LEFT JOIN
    (SELECT
        user_id
        , joined
        , year(joined) as 'j_year'
        , month(joined) as 'j_month'
    FROM
        USER_INFO
    WHERE
        year(joined) = 2021
    ) u
        ON o.user_id = u.user_id
;

어떤 상품이든 구매만 했다면 '구매한 회원'으로 처리되므로
product_id로 구분할 필요 없이 user_id마다 년월별로 1개씩만 뽑으면 될 듯 하다
distinct보다는 group by로 처리하는 게 나을듯

SELECT
    o.user_id as 'total_users'
    , u.user_id as 'buying_users'
    , j_year
    , j_month
FROM
    (SELECT
        user_id
    FROM
        ONLINE_SALE
    ) o
    LEFT JOIN
    (SELECT
        user_id
        , joined
        , year(joined) as 'j_year'
        , month(joined) as 'j_month'
    FROM
        USER_INFO
    WHERE
        year(joined) = 2021
    ) u
        ON o.user_id = u.user_id
GROUP BY
    1, 3, 4
;

일단 count와 ratio를 구해보기로 했다

SELECT
#    o.user_id as 'total_users'
    j_year
    , j_month
    , count(u.user_id) as 'purchased_users'
    , round(count(u.user_id) / count(*), 2) as 'purchased_ratio'
    
FROM
    (SELECT
        user_id
    FROM
        ONLINE_SALE
    ) o
    LEFT JOIN
    (SELECT
        user_id
        , joined
        , year(joined) as 'j_year'
        , month(joined) as 'j_month'
    FROM
        USER_INFO
    WHERE
        year(joined) = 2021
    ) u
        ON o.user_id = u.user_id
GROUP BY
    o.user_id, 1, 2
;


ratio가 전부 1.00으로 나온다
분자와 분모가 같다는 의미...
count(칼럼명)이라 null값은 빼고 count해줄 거라고 생각했는데,
group by로 묶이면서 달라졌나?

발상을 잘못했던 것 같다
USER_INFO 테이블에서 year, month별 count(user_id)를 하면 그게 가입 회원수고,
ONLINE_SALE 테이블에서 year, month별 count(user_id)를 하면 그게 구매 회원수다
다시 처음부터 짜보자

먼저 2021년 기준 년월별 가입 회원수는 아래와 같다

SELECT
    year(joined) as 'j_year'
    , month(joined) as 'j_month'
    , count(distinct user_id) as 'cnt_join'
FROM
    user_info
WHERE
    year(joined) = 2021
GROUP BY
    1, 2
;

다음으로 2021년 가입한 유저들의 년월별 구매 횟수는 아래와 같다
문제에서 묻는 바는 이게 아니었다

SELECT
    o.user_id
    , year(sales_date) as 's_year'
    , month(sales_date) as 's_month'
    , count(o.sales_date) as 'cnt_sales'
FROM
    user_info u
    INNER JOIN online_sale o ON u.user_id = o.user_id
WHERE 
    year(u.joined) = 2021
GROUP BY
    o.user_id, 2, 3
;

문제를 잘못 이해했었다
다시 정리한다

  1. 2021년에 가입한 전체 회원 수를 구한다
  2. 그 user_id를 갖고 online_sale과 JOIN한다
  3. 년월별로 count()한다
  4. 1을 분모로하여 매년월별 상품을 구매한 비율을 구한다

1. 2021년에 가입한 전체 회원 수와 그 id를 구한다

2021년에 가입한 전체 회원 수

SELECT
    count(user_id) as 'joined_users'
FROM
    user_info
WHERE
    year(joined) = 2021

2021년에 가입한 user_id

SELECT
    user_id
FROM
    user_info
WHERE
    year(joined) = 2021

2. 그 user_id를 갖고 online_sale과 JOIN한다

SELECT
    u.user_id
    , year(sales_date) as 's_year'
    , month(sales_date) as 's_month'
FROM (
    SELECT
        user_id
    FROM
        user_info
    WHERE
        year(joined) = 2021
    ) u
    INNER JOIN online_sale o
        ON u.user_id = o.user_id

3. 년월별로 count()한다

SELECT
    year(sales_date) as 's_year'
    , month(sales_date) as 's_month'
    , count(u.user_id) as 'purchased_users'
FROM (
    SELECT
        user_id
    FROM
        user_info
    WHERE
        year(joined) = 2021
    ) u
    INNER JOIN online_sale o
        ON u.user_id = o.user_id
GROUP BY
    1, 2

4. 1을 분모로하여 매년월별 상품을 구매한 비율을 구한다

SELECT
    year(sales_date) as 's_year'
    , month(sales_date) as 's_month'
    , count(u.user_id) as 'purchased_users'
    , round(count(u.user_id) / (SELECT
                            count(user_id) as 'joined_users'
                        FROM
                            user_info
                        WHERE
                            year(joined) = 2021
                         ), 1) as 'purchased_ratio'
FROM (
    SELECT
        user_id
    FROM
        user_info
    WHERE
        year(joined) = 2021
    ) u
    INNER JOIN online_sale o
        ON u.user_id = o.user_id
GROUP BY
    1, 2
ORDER BY
    1, 2

...이게 아니라고?

SELECT
    year(sales_date) as 'YEAR'
    , month(sales_date) as 'MONTH'
    , count(distinct u.user_id) as 'PURCHASED_USERS'
    , round(count(distinct u.user_id) / (SELECT
                            count(distinct user_id) as 'joined_users'
                        FROM
                            user_info
                        WHERE
                            year(joined) = 2021
                         ), 1) as 'PUCHASED_RATIO'
FROM (
    SELECT
        user_id
    FROM
        user_info
    WHERE
        year(joined) = 2021
    ) u
    INNER JOIN online_sale o
        ON u.user_id = o.user_id
GROUP BY
    1, 2
ORDER BY
    1, 2

는 distinct 이슈였다

count(distinct u.userid) as 'PURCHASED_USERS'
round(count(distinct u.user_id) (하략)_
SELECT count(distinct user_id) as 'joined_users'

이렇게 3군데에 추가로 distinct를 넣어줬는데,
user_id가 당연히 unique constraint를 만족했을 거라고 생각했던 내가 안일했던 것이겠지

SQL 라이브세션 과제에서도 distinct를 안 넣은 큰 실수를 했었는데
링크의 2번
데이터 전처리 과정에서 user_id의 중복 여부 및 결측값 체크도 해야하듯이,
그렇지 않더라도 현업에서 데이터 중복 및 누락이 종종 발생한다는 점을 고려한다면,
ID 변수를 사용할 때는 distinct를 빼먹지 않도록 유의해야 할 것!

profile
양승우
어제보다 오늘 더
이전 포스트

[SQL] 코드카타 10일차

다음 포스트

[SQL] 코드카타 3주 (화)

0개의 댓글