[TIL] 이커머스 데이터로 전환율, 객단가 구하기!

해당 쿼리를 작성하며 놓치거나 배웠던 점

나눗셈 계산 시 분모에 들어갈 자리에는 NUllIF함수를 통해 이상치 및 결측치를 제거할 수 있다.
전환율을 구하는 데에 JOIN문을 쓰려면 INNER JOIN보다는 LEFT JOIN을 쓰는 것이 맞다.
(view - cart - purchase 순으로 깔때기가 점점 작아지기 때문)

SELECT 
    a.brand, 
    a.count_view, -- 상품 조회자 수 --
    b.count_cart, -- 장바구니 유저 수 --
    c.count_purchase, -- 구매자 수 --
    (b.count_cart / NULLIF(a.count_view, 0)) * 100 AS CVR_cart, -- 장바구니 전환율 --
    (c.count_purchase / NULLIF(b.count_cart, 0)) * 100 AS CVR_purchase, -- 구매 전환율 -- 
    (c.count_purchase / NULLIF(a.count_view, 0)) * 100 AS CVR_total, --전체 구매 전환율 
    d.total_selling_price, -- 전체 매출 --
    d.total_selling_price / NULLIF(c.count_purchase, 0) AS Avg_selling_price -- 객단가 --
FROM 
 	-- 브랜드 별 상품 조회자 수를 구하는 쿼리문 --
    (SELECT 
        brand, 
        COUNT(DISTINCT user_id) AS count_view
    FROM 
        events
    WHERE 
        event_type = 'VIEW' 
        AND brand IS NOT NULL 
        AND brand != ''
    GROUP BY 
        brand) a
    -- 브랜드 별 장바구니 유저 수를 구하는 쿼리문 --    
LEFT JOIN 
    (SELECT 
        brand, 
        COUNT(DISTINCT user_id) AS count_cart
    FROM 
        events
    WHERE 
        event_type = 'cart' 
        AND brand IS NOT NULL 
        AND brand != ''
    GROUP BY 
        brand) b ON a.brand = b.brand
   -- 브랜드 별 상품 구매자 수를 구하는 쿼리문 --
LEFT JOIN 
    (SELECT 
        brand, 
        COUNT(DISTINCT user_id) AS count_purchase
    FROM 
        events
    WHERE 
        event_type = 'purchase' 
        AND brand IS NOT NULL 
        AND brand != ''
    GROUP BY 
        brand) c ON a.brand = c.brand
   -- 브랜드 별 전체 매출을 구하는 쿼리문--
LEFT JOIN 
    (SELECT 
        brand, 
        ROUND(SUM(price), 1) AS total_selling_price
    FROM 
        events e
    WHERE 
        event_type = 'purchase' 
        AND brand IS NOT NULL 
        AND brand != ''
    GROUP BY 
        brand) d ON a.brand = d.brand
ORDER BY 
    total_selling_price DESC;