#74. JOIN : 상품을 구매한 회원 비율 구하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. 

SELECT 
    YEAR, 
    MONTH, 
    COUNT(*) AS PUCHASED_USERS,
	ROUND((COUNT(*)/
           (
               SELECT COUNT(*)
			   FROM USER_INFO 
               WHERE YEAR(JOINED) = 2021
           )), 1) AS PUCHASED_RATIO
FROM (
    SELECT 
        DISTINCT YEAR(S.SALES_DATE) AS YEAR, 
        MONTH(S.SALES_DATE) AS MONTH, 
        U.USER_ID
    FROM ONLINE_SALE AS S
        JOIN USER_INFO AS U 
        ON S.USER_ID = U.USER_ID 
    AND YEAR(JOINED) = 2021
) AS A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

 

2.

SELECT 
    DATE_FORMAT(o.SALES_DATE, '%Y') 'YEAR', 
    DATE_FORMAT(o.SALES_DATE, '%m') 'MONTH', 
    COUNT(DISTINCT(o.USER_ID)) 'PUCHASED_USERS',
    ROUND(COUNT(DISTINCT(o.USER_ID)) / 
          (SELECT COUNT(DISTINCT(u.USER_ID)) 
           FROM USER_INFO AS u 
           WHERE DATE_FORMAT(u.JOINED, '%Y') = 2021), 1) AS 'PUCHASED_RATIO'
FROM ONLINE_SALE AS o, USER_INFO AS u
WHERE o.USER_ID = u.USER_ID and DATE_FORMAT(u.JOINED, '%Y') = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

코드 설명

#SELECT #DATE_FORMAT #COUNT #DISTINCT #ROUND #GROUP BY #ORDER BY #JOIN

 

 

문제 출처

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