[SQL] Programmers_JOIN. 상품을 구매한 회원 비율 구하기

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요.

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림 해주세요.

전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

2. 문제 풀이

2-1. 최종 풀이


WITH TOTAL_USERS AS (
     SELECT COUNT(USER_ID) AS TOTAL_USER_COUNT
     FROM USER_INFO
     WHERE YEAR(JOINED) = 2021
)

SELECT YEAR(OS.SALES_DATE) AS YEAR,
       MONTH(OS.SALES_DATE) AS MONTH,
       COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT OS.USER_ID)/TU.TOTAL_USER_COUNT, 1) AS PUCHASED_RATIO
FROM USER_INFO AS UI
JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID
CROSS JOIN TOTAL_USERS AS TU
WHERE YEAR(UI.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

2-2. 단계별 풀이

2-2-1. 첫 번째 쿼리(테이블) 만들기


WITH TOTAL_USERS AS (
     SELECT COUNT(USER_ID) AS TOTAL_USER_COUNT
     FROM USER_INFO
     WHERE YEAR(JOINED) = 2021
)

해당 코드를 실행시키면, USER_INFO 테이블의 2021년에 가입한 회원 전체 수를 반환한다.

2-2-2. 두 번째 쿼리 만들기


SELECT YEAR(OS.SALES_DATE) AS YEAR,
       MONTH(OS.SALES_DATE) AS MONTH,
       COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT OS.USER_ID)/TU.TOTAL_USER_COUNT, 1) AS PUCHASED_RATIO
FROM USER_INFO AS UI
JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID
CROSS JOIN TOTAL_USERS AS TU
WHERE YEAR(UI.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

해당 코드를 실행시키면, USER_INFO, ONLINE_SALE, TOTAL_USERS 테이블을 2021년에 가입한 값을 기준으로, 연/월 별로 그룹화하고 오름차순으로 정리된다.

이때 연/월은 SALES_DATE를 기반으로 각각 연도와 월을 뽑아낸다.

상품을 구매한 고유 회원수와, 상품을 구매한 회원의 비율을 출력한다.

2-2-3. 최종 쿼리


WITH TOTAL_USERS AS (
     SELECT COUNT(USER_ID) AS TOTAL_USER_COUNT
     FROM USER_INFO
     WHERE YEAR(JOINED) = 2021
)

SELECT YEAR(OS.SALES_DATE) AS YEAR,
       MONTH(OS.SALES_DATE) AS MONTH,
       COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT OS.USER_ID)/TU.TOTAL_USER_COUNT, 1) AS PUCHASED_RATIO
FROM USER_INFO AS UI
JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID
CROSS JOIN TOTAL_USERS AS TU
WHERE YEAR(UI.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

해당 코드를 실행시키면, USER_INFO 테이블의 2021년에 가입한 회원 전체 수를 반환하는 테이블을 만든다.

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 결과를 반환한다.

[알고리즘] 최단 경로(Shortest Path) 알고리즘 [A/B test] 종합 대조 실험에 대한 보완 기법들

Chang Hyewon