코테준비 | Active User Retention /mau 조회하기(postgreSQL)

☑️ Active User Retention

[문제]

• 2022년 7월 mau(월간 활성 사용자수 ) 조회하기 
• 7월 월간 사용자는 전월에도 활성 유저 여야함 

---

[문제 풀이]

내 코드

SELECT EXTRACT('MONTH' FROM event_date) AS month
      ,COUNT(DISTINCT user_id) AS monthly_active_users
FROM user_actions
WHERE event_type IN ('sign-in', 'like', 'comment')
AND TO_CHAR(event_date,'YYYY-MM') ='2022-07'
AND user_id IN (SELECT DISTINCT user_id
                FROM user_actions
                WHERE event_type IN ('sign-in', 'like', 'comment')
                  AND TO_CHAR(event_date,'YYYY-MM') ='2022-06')
GROUP BY EXTRACT('MONTH' FROM event_date)

 

수정 코드 

SELECT EXTRACT(MONTH FROM event_date) AS month
      ,COUNT(DISTINCT user_id) AS monthly_active_users
FROM user_actions AS curr
WHERE event_type IN ('sign-in', 'like', 'comment')
    AND TO_CHAR(event_date,'YYYY-MM') ='2022-07'
    AND EXISTS ( SELECT last.user_id 
                 FROM user_actions AS last
                 WHERE last.user_id = curr.user_id
                   AND EXTRACT(MONTH FROM last.event_date) =
                   EXTRACT(MONTH FROM curr.event_date - interval '1 month')
    			)
GROUP BY EXTRACT(MONTH FROM event_date)

 

이슈 및 해결 과정

• 내코드 아이디어 :  where절 서브쿼리로 2022-6월 활성사용자수 필터링한 후 2022-7월 월간 활성자 수 집계
  • event_date : datetime type (ex 05/31/2022 12:00:00 ) 에서 년-월만 추출하기
    • `To_char(event_date,'YYYY-MM')` 하면 `2022-07` 반환됨
  • `where in 다중행 서브쿼리`로 2022-06 월 활성유저가 2022-07 아우터 쿼리유저랑 같은 거만 집계
  • 특정 년/월 만 추출할때는 : EXTRACT(YEAR FROM event_date) / EXTRACT(MONTH FROM event_date)
• 수정 코드 아이디어 : WHERE 절 `EXISTS` 서브쿼리 활용  ▼상세설명

-- WHERE EXISTS (서브쿼리) 
-- 문제에서 2022-07월 활성사용자가 2022-06월에도 활성사용자 이여야 하므로 위 함수 사용
AND EXISTS ( SELECT last.user_id --3. 필터링 된 유저가 exists 있으면 그것만 필터링
             FROM user_actions AS last
             WHERE last.user_id = curr.user_id -- 1. 연관 서브쿼리로 아우터 쿼리 user 연결 
               AND EXTRACT(MONTH FROM last.event_date) =
               EXTRACT(MONTH FROM curr.event_date - interval '1 month') -- 2.서브쿼리의 날짜가(6월) 아우터쿼리(7월) 보다 1달 전인
    			)

• where 절 서브쿼리로 필터링하기
  • WHERE EXISTS (서브쿼리) : '있다/없다' 만 빠르게 판별해주는 서브쿼리 → 필요한 레코드만 검색하여 필터링 하기때문에 훨씬 효율적임 
  • WHERE 컬럼 IN (서브쿼리 ) : 서브쿼리 전체를 스캔한 후 필터링

🔹 정리: 두 쿼리 비교

비교	where in	where exsist
6월 활동한 유저 필터링 방식	IN (SELECT DISTINCT user_id ... )	WHERE EXISTS (SELECT last.user_id ...)
성능 최적화	서브쿼리에서 DISTINCT 연산이 발생하여 비효율적	EXISTS() 사용으로 `필요한 레코드만 검색`하여 최적화됨
실행 속도	데이터가 많아지면 느려질 가능성이 큼	서브쿼리 실행 최적화로 빠르게 동작
로직	IN (서브쿼리) 는 전체 목록을 가져와서 비교	EXISTS (서브쿼리) 는 한 개라도 발견되면 즉시 TRUE 반환

 

한줄 포인트

• where in (스칼라 서브쿼리) 사용할 때 where exsist ( 서브쿼리) 로 하면 훨씬 효율 적임 
• where exsist 는 필요한 레코드만 검색하여 해당되는경우 즉시 true 반환해줌
• 날짜의 특정 부분을 출력할때 :
  • to_char(컬럼,'YYYY-MM)
  • extract(year from 컬럼) , extract(month from 컬럼) : 각각 필터링 해줌

Facebook SQL Interview Question | DataLemur