[프로그래머스] 상품을 구매한 회원 비율 구하기

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

프로그래머스

 

WITH USER_2021 AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE DATE_FORMAT(JOINED, '%Y') = '2021'
)

SELECT
    YEAR,
    MONTH,
    PURCHASED_USERS,
    ROUND(PURCHASED_USERS/JOINED_USERS, 1) AS PUCHASED_RATIO
FROM (
    SELECT
        DATE_FORMAT(SALES_DATE, '%Y') AS YEAR,
        DATE_FORMAT(SALES_DATE, '%m') AS MONTH,
        (SELECT COUNT(*) FROM USER_2021) AS JOINED_USERS,
        COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS
    FROM USER_2021 A
    JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
    GROUP BY 1, 2, 3
    ORDER BY 1, 2, 3
) T

 

알아야 할 점

- SELECT 내 SELECT 가능하며 괄호 필수