USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
SELECT
YEAR(B.SALES_DATE) AS YEAR,
MONTH(B.SALES_DATE) AS MONTH,
COUNT(DISTINCT A.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(1) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM
USER_INFO AS A
LEFT JOIN ONLINE_SALE AS B ON A.USER_ID = B.USER_ID
WHERE
YEAR(A.JOINED) = 2021
AND B.SALES_DATE IS NOT NULL
GROUP BY
YEAR(B.SALES_DATE), MONTH(B.SALES_DATE)
ORDER BY
YEAR(B.SALES_DATE), MONTH(B.SALES_DATE)
SELECT
AAA.YEAR,
AAA.MONTH,
COUNT(AAA.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(AAA.USER_ID) / (SELECT COUNT(1) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PUCHASED_RATIO
FROM
(
SELECT
AA.YEAR,
AA.MONTH,
AA.USER_ID
FROM
(
SELECT
A.USER_ID,
YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH
FROM
USER_INFO AS A
LEFT OUTER JOIN ONLINE_SALE AS B ON A.USER_ID = B.USER_ID
WHERE
YEAR(A.JOINED) = 2021
AND SALES_DATE IS NOT NULL
) AS AA
GROUP BY
AA.USER_ID, AA.YEAR, AA.MONTH
) AS AAA
GROUP BY
AAA.MONTH, AAA.YEAR
ORDER BY
AAA.YEAR,AAA.MONTH
이 문제는 의류 쇼핑몰의 데이터베이스에서, 2021년에 가입한 회원들 중에서 상품을 구매한 회원 수와 전체 가입 회원 대비 상품을 구매한 회원 비율을 연도와 월별로 계산하여 출력하는 문제다.
먼저, 회원 정보를 담고 있는 USER_INFO 테이블에서 2021년에 가입한 회원만을 추출합니다. 이후, 이 데이터를 온라인 상품 판매 정보를 담고 있는 ONLINE_SALE 테이블과 조인하여 해당 회원들이 상품을 구매했는지 확인한다. 여기서 상품을 구매한 것으로 간주되는 조건은 ONLINE_SALE 테이블에 해당 회원의 USER_ID와 관련된 판매 데이터가 존재하고, 판매일(SALES_DATE)이 기록되어 있는 경우다.
구매한 회원의 데이터를 연도와 월별로 그룹화한 후, 각 연도와 월에 상품을 구매한 회원 수를 집계합니다. 이때, 동일한 회원이 여러 번 구매했더라도 중복되지 않도록 각 회원의 구매 여부를 한 번만 카운트한다.
그다음, 2021년에 가입한 전체 회원 수를 기준으로, 해당 월에 구매한 회원 수를 나누어 구매 비율을 계산합니다. 이 비율은 소수점 둘째 자리에서 반올림하여 나타낸다. 마지막으로, 결과는 연도를 기준으로 오름차순으로 정렬하고, 연도가 동일한 경우에는 월을 기준으로 오름차순으로 정렬하여 출력된다.
이 과정을 통해 2021년에 가입한 회원들의 구매 행동을 연도와 월별로 분석하고, 이를 기반으로 마케팅이나 운영 전략에 활용할 수 있는 데이터를 제공하게 된다.
https://school.programmers.co.kr/learn/courses/30/lessons/131534