Active User Retention

[문제]

https://datalemur.com/questions/user-retention

Facebook SQL Interview Question | DataLemur

 

[풀이]

1. 기존 테이블에서 최신 월을 가져온다

2. 현재 월에서 event_type이 3개인 유저만 가져온다

3. 현재 월을 가져오고 유저 수를 계산해준다

 

 

[코드]

WITH current_month AS (
  SELECT MAX(EXTRACT(MONTH FROM event_date)) AS month
  FROM user_actions
), recent_actions AS (
  SELECT user_id
  FROM user_actions
  WHERE 
    EXTRACT(YEAR FROM event_date) = (SELECT MAX(EXTRACT(YEAR FROM event_date)) FROM user_actions)
    AND 
    EXTRACT(MONTH FROM event_date) IN (
      (SELECT month FROM current_month), (SELECT month FROM current_month) - 1
    )
  GROUP BY user_id
  HAVING COUNT(DISTINCT event_type) = 3
)
SELECT
  (SELECT month FROM current_month) AS month
  , COUNT(user_id) as monthly_active_users
FROM recent_actions