데.분.레 - 복습 13

사용자의 방문 빈도 집계하기

WITH action_log_with_dt AS(
  SELECT *
    , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
  )
, action_day_count_per_user AS (
  SELECT user_id
       , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
    GROUP BY user_id
  )

SELECT action_day_count
     , COUNT(DISTINCT user_id) AS user_count
  FROM action_day_count_per_user
  GROUP BY action_day_count
  ORDER BY action_day_count

구성비와 구성비 누계도 추가로 구할 수 있다.

WITH action_log_with_dt AS(
  SELECT *
    , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
  )
, action_day_count_per_user AS (
  SELECT user_id
       , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
    GROUP BY user_id
  )
SELECT action_day_count
     , COUNT(DISTINCT user_id) AS user_count
     , 100.0
       * COUNT(DISTINCT user_id)/SUM(COUNT(DISTINCT user_id)) OVER()
       AS composition_ratio
     , 100.0
       * SUM(COUNT(DISTINCT user_id)) OVER(ORDER BY action_day_count
                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       / SUM(COUNT(DISTINCT user_id)) OVER()
       AS cumulative_ratio
  FROM action_day_count_per_user
  GROUP BY action_day_count
  ORDER BY action_day_count