데.분.레 - 복습 14

벤 다이어그램으로 사용자 액션 집계하기

WITH user_action_flag AS (
  SELECT user_id
       , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
       , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review
       , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
     FROM action_log
     GROUP BY user_id
     )
SELECT *
  FROM user_action_flag

사용자 단위로 purchase, review, favorite 이라는 3개의 액션을 행한 로그가 존재하는지를 0과 1의 플래그로 부여하였다.
SIGN : 함수 안의 인자가 음수면 -1, 0이면 0, 양수면 1을 반환하는 함수

WITH user_action_flag AS (
  SELECT user_id
       , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
       , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review
       , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
     FROM action_log
     GROUP BY user_id
     )
, action_venn_diagram AS (
  SELECT has_purchase
       , has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_review, has_favorite
  
  UNION ALL
  SELECT NULL AS has_purchase
       , has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_review, has_favorite
  
  UNION ALL
  SELECT has_purchase
       , NULL AS has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_favorite
    
  UNION ALL
  SELECT has_purchase
       , has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_review
  
  UNION ALL
  SELECT NULL AS has_purchase
       , NULL AS has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_favorite

  UNION ALL
  SELECT NULL AS has_purchase
       , has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_review

  UNION ALL
  SELECT has_purchase
       , NULL AS has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase
  
  UNION ALL
  SELECT NULL AS has_purchase
       , NULL AS has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
)
              
SELECT *
  FROM action_venn_diagram
  ORDER BY has_purchase, has_review, has_favorite