PostgreSQL 풀이 | Active User Retention

문제 1.  Active User Retention

Assume you're given a table containing information on Facebook user actions. Write a query to obtain number of monthly active users (MAUs) in July 2022, including the month in numerical format "1, 2, 3".

Hint:
An active user is defined as a user who has performed actions such as 'sign-in', 'like', or 'comment' in both the current month and the previous month.

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

Facebook SQL Interview Question | DataLemur

---

💡 문제 풀이 

`첫 풀이` -> 개선 필요!

소요 시간 34:30

with t1 as(
SELECT user_id, 
  EXTRACT (MONTH FROM event_date) AS month,
  LAG(EXTRACT (MONTH FROM event_date)) over(PARTITION BY user_id) AS previous_month
FROM user_actions
WHERE EXTRACT (YEAR FROM event_date) = 2022
  AND event_type in ('sign-in','like','comment') -- 조건이 되는 유저 행동(event_type)
GROUP BY user_id, month -- 이건 왜 썼지.. 필요없는 코드
),
t2 as(
SELECT month, user_id, count(distinct user_id) AS active_users_num
FROM t1
WHERE previous_month IS NOT NULL 
  AND month-previous_month=1  -- 바로 이전 달에도 활동한 사용자
  AND month = 7  -- 2022년 7월의 MAU만 출력
GROUP BY month, user_id -- 이건 왜 썼지.. 필요없는 코드
)
SELECT month, sum(active_users_num) AS monthly_active_users
FROM t2
GROUP BY month;

`오답노트`

• 이런저런 코드를 수정해가면서 코드를 짜다보니까 윈도우함수에 불필요한 Group by를 해둠;;
• 2번째 with문(t2)도 세번째랑 합칠 수 있음

`개선한 코드`

WITH t1 AS (
  SELECT user_id, 
         EXTRACT(MONTH FROM event_date) AS month,
         LAG(EXTRACT(MONTH FROM event_date)) OVER (PARTITION BY user_id ORDER BY event_date) AS previous_month
  FROM user_actions
  WHERE EXTRACT(YEAR FROM event_date) = 2022
    AND event_type IN ('sign-in', 'like', 'comment') -- 조건이 되는 유저 행동(event_type)
)
SELECT month, COUNT(DISTINCT user_id) AS monthly_active_users
FROM t1
WHERE previous_month IS NOT NULL 
  AND month - previous_month = 1  -- 바로 이전 달에도 활동한 사용자
  AND month = 7  -- 2022년 7월의 MAU만 출력
GROUP BY month;

---

💯결과