[250901] 스파르타코딩 본캠프 20일차 (2)

💥  CODEKATA                                                                                                           

~ 76문제

 

 

 

76) 상품을 구매한 회원 비율 구하기

WITH 
joined_2021 AS (
    SELECT COUNT(DISTINCT user_id) as cnt
    FROM user_info
    WHERE joined LIKE "2021-%"
),
almost AS (
    SELECT a.year, a.month, COUNT(DISTINCT a.user_id) AS purchased_users
    FROM (
        SELECT 
            DATE_FORMAT(o.sales_date, "%Y") as year,
            DATE_FORMAT(o.sales_date, "%m") as month,
            o.online_sale_id, o.user_id
        FROM online_sale o JOIN user_info u ON o.user_id = u.user_id AND u.joined LIKE "2021-%"
    ) a 
    GROUP BY 
        a.month, a.year
)
SELECT a.year, a.month, a.purchased_users, ROUND((a.purchased_users/ j.cnt), 1) AS puchasratio
FROM almost a, joined_2021 j