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

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

문제 : 상품을 구매한 회원 비율 구하기

문제 설명
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

Column_name	Type	Nullable
USER_ID	INTEGER	FALSE
GENDER	TINYINT(1)	TRUE
AGE	INTEGER	TRUE
JOINED	DATE	FALSE

GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

Column_name	Type	Nullable
ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

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

문제 접근

with JOINED_2021 as (
    select distinct USER_ID, JOINED
    from USER_INFO
    where JOINED > '2020-12-31' and JOINED < '2022-01-01'
)
select distinct year(sales_date) YEAR, 
month(sales_date) MONTH, 
count(*) PURCHASED_USERS,
round(count(*) / (Select distinct count(*) from joined_2021 ),1) PUCHASED_RATIO
from joined_2021 j join ONLINE_SALE o 
on j.USER_ID = o.USER_ID
group by 1,2
order by 1,2

실행 결과

YEAR	MONTH	PURCHASED_USERS	PUCHASED_RATIO
2022	1	59	0.4
2022	2	45	0.3
2022	3	7	0.0

이게 정답인 줄 알았는데, 왜 틀렸는지 이유를 도저히 모르겠어서, 예전에 풀었던 쿼리를 보고 정답을 유추해보려고 했는데 실패했다. 우선 실패한 이유는, distinct 사용법과 Count(*) 의 특징을 제대로 이해하지 못했던 것인데,

count(*)를 하기 전에 distinct를 쓰면 Count(*)안에 중복들도 없어질 거라고 아무런 의심도 없이 사용했고, 사실 distinct가 중복을 없애준다고는 알았지만, 제대로 활용해본 적은 없었던 것 같다.

여튼 비교를 해보자면,
오답쿼리의 count(*)는 한 명의 회원이 같은 달에 상품을 2개 구매했을 경우, 구매자 수를 2명으로 카운트 할 수 있다. 상품을 구매한 회원수를 알고 싶다면
count(*) 대신 count(distinct o.USER_ID)를 사용해야 한다.

중요한 점은
SELECT DISTINCT COUNT(*)에서 DISTINCT는 아무런 역할을 하지 못하는데, 그 이유는 COUNT(*)는 이미 단일 숫자 결과값이기 때문.

내 풀이

SELECT YEAR(sales_date) YEAR, MONTH(sales_date) MONTH,
COUNT(DISTINCT o.USER_ID) PURCHASED_USERS,
ROUND(COUNT(DISTINCT o.USER_ID) / 
      (SELECT COUNT(*)
      FROM USER_INFO
      WHERE JOINED LIKE '2021%') , 1) PUCHASED_RATIO
FROM USER_INFO u JOIN ONLINE_SALE o
ON u.USER_ID = o.USER_ID
WHERE u.JOINED LIKE '2021%'
GROUP BY 1,2
ORDER BY 1,2

다른 사람 풀이

CTE 사용

with JOINED_2021 as (
    select distinct USER_ID, JOINED
    from USER_INFO
    where JOINED > '2020-12-31' and JOINED < '2022-01-01'
)
select year(sales_date) YEAR, 
month(sales_date) MONTH, 
count(distinct o.USER_ID) PURCHASED_USERS,
round(count(distinct o.USER_ID) / (Select distinct count(*) from joined_2021 ),1) PUCHASED_RATIO
from joined_2021 j join ONLINE_SALE o 
on j.USER_ID = o.USER_ID
group by 1,2
order by 1,2

쉬운 문제인 줄 알았는데, 어려운 문제였다.

---

2026-02-16 다시 풀어보기

1차 시도, 실패.

# leftjoin 써서, count( distinct u.user_id), count( distinct o.user_id) / count( distinct u.user_id) 이런 식으로 계산하면 되지 않을까?
# gruop by 를 연, 월로 쓰면, 가입한 인원수가 월 별로 바뀌지 않을까?
# partition 쓰면 되지 않을까

select Year(SALES_DATE) YEAR , month(SALES_DATE) MONTH,
count(o.user_id) over( partition by Year(SALES_DATE), month(SALES_DATE)) PURCHASED_USERS
from user_info u left join online_sale o
on u.user_id = o.user_id
and u.joined >= '2021-01-01' and u.joined <= '2021-12-31'
group by 1,2
having year is not null

모집단과 집계 단위를 혼동했다. 2021년 가입자를 분모로 고정해야 하는데, LEFT JOIN 후 월별로 GROUP BY를 하면서 분모까지 월 단위로 줄어들 수 있는 구조를 만들어 버림.
또한 OVER()를 사용해 해결하려 했지만, 윈도우 함수는 행을 줄이지 않고 집계값을 붙이는 방식이라 월별 요약 결과를 만드는 문제와 맞지 않았다.
결국 핵심 오류는 분모를 집계 이전에 고정하지 못한 것과, 윈도우 함수의 동작 방식을 정확히 이해하지 못했던 점이었음.

2차 시도

# 출력 grain = 월
# 모집단 = 2021 가입자
# 계산 대상 = 월별 구매자 수
# 필요한 distinct 여부 = user distinct 필요
# 집계 방식 = gruop by

with u2021 as (
    select user_id
    from USER_INFO u
    where joined between '2021-01-01' AND '2021-12-31'
)
select Year(sales_date) YEAR,
month(sales_date) MONTH,
count(distinct o.user_id) PURCHASED_USERS ,
round( count(distinct o.user_id) / (select count(*) from u2021), 1)PUCHASED_RATIO
from u2021 u join ONLINE_SALE o
on u.user_id = o.user_id
group by Year(sales_date), month(sales_date)
order by Year(sales_date), month(sales_date)