[SQL코테] 상품을 구매한 회원 비율 구하기

SQL도 기억할겸 프로그래머스에 있는 SQL 문제를 풀어본다.

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

SELECT YEAR(SALES_DATE) as YEAR, 
MONTH(SALES_DATE) as MONTH, 
COUNT(distinct USER_ID) as PUCHASED_USERS,
ROUND(COUNT(distinct USER_ID)/Total_USER, 1) as PUCHASED_RATIO
FROM
    (
    SELECT b.SALES_DATE as SALES_DATE,
    b.USER_ID as USER_ID,
    a.Total_USER as Total_USER
    FROM 
        (
        SELECT distinct USER_ID, COUNT(USER_ID)OVER() as Total_USER
        FROM USER_INFO
        WHERE JOINED < '2022-01-01'
        ) as a 

        INNER JOIN

        (
        SELECT *
        FROM ONLINE_SALE
        WHERE SALES_DATE >= '2022-01-01'
        ) as b
        ON a.USER_ID = b.USER_ID
    ) as c
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH

Share this post