active: 최근 90일 이내 방문한 유저

bigquery에서 최근 90일 이내 방문한 유저 수를 뽑으려 한다.

 

처음엔 rolling window 사용해서 over order by 기능을 썼지만, 이렇게 되면 count(distinct user_id)를 할 수가 없었다...ㅜ

계속 방법을 검색하다가 발견한 글....!!

How to do rolling count distinct in BigQuery? Analytic function is not a good solution

 

속도도 빠르고 좋아요~

 

WITH login as (select
CAST(created_at AS DATE) as `sess_created_at`,
-- UNIX_DATE(CAST(created_at AS DATE)) `udate`,
user_id
from `org_data.events`
where user_id is not null
),

min_max AS (
  SELECT
    MIN(sess_created_at) AS min_date,
    MAX(sess_created_at) AS max_date
  FROM
    login ),

member_90_day_window AS (
  SELECT
    user_id,
    sess_created_at AS base_date,
    GENERATE_DATE_ARRAY(sess_created_at, DATE_ADD(sess_created_at, INTERVAL 89 DAY), INTERVAL 1 DAY) AS rollingwindow
  FROM
    login)

SELECT
  sess_created_at,
  COUNT(DISTINCT user_id) AS active
FROM
  member_90_day_window, UNNEST(rollingwindow) AS sess_created_at
WHERE sess_created_at <= (SELECT max_date FROM min_max)
--WHERE sess_created_at BETWEEN (SELECT DATE_ADD(min_date, INTERVAL 89 DAY) FROM min_max)
--  AND (SELECT max_date FROM min_max)
GROUP BY sess_created_at
ORDER BY sess_created_at desc

 

최근 90일 이내 -> 과거 90일이라고 생각을 해서, DATE_SUB으로 바꿔주었더니 sess_created_at이 과거 90일 시점이었다...!

왼쪽부터 일일 방문자 수 / data_add (원하는 결과) / data_sub

 

고유 유저 수만 뽑아줬을 때, 1월 11일부터 300명 정도씩 늘어나다가 14일에 약 400명, 15일에 700명 정도가 증가했다.