solvesql | 난이도 4 | Stickiness

사용자 고착도(=Stickiness)

 

Stickiness를 알기 전에, 우선 dau/wau/mau 개념이 필요하다.

• DAU(Daily Active User): 일간 활성 사용자 수
• WAU(Weekly Active User): 주간 활성 사용자 수
• MAU(Monthly Active User): 월간 활성 사용자 수

일반적으로 Stickiness, 즉 사용자 고착도 지표는 dau를 wau 또는 mau로 나누어 정의한다. 이는 사용자가 특정 서비스나 플랫폼에 얼마나 지속적으로 참여하고 있는지를 측정하는 중요한 지표이다. 이것이 중요한 이유는 이 지표가 높을수록 사용자가 서비스를 떠날 가능성이 낮아지는 것으로 해석할 수 있기 때문이다. 

 

아래 solvesql 문제 해결을 통해 Stickiness 지표를 계산해보자.

 

 온라인 쇼핑몰의 Stickiness

 

온라인 쇼핑몰에서는 주문 완료 액션을 중요하게 생각하기 때문에 해당 기간 동안 한 번이라도 주문한 고객을 ‘활성 고객'으로 정의할 것. 

 

예를 들어 2020년 11월 8일의 dau는 해당 일자에 주문을 했던 고객 수이고, wau는 11월 2일부터 8일 사이에 한 번이라도 주문한 고객의 수이다. 이 날의 고착도는 wau 대비 dau 비율이다.

 

문제 조건

1. 2020년 11월 한 달 동안의 일별 DAU, WAU, Stickiness를 계산

2. Stickiness는 반올림하여 소수점 둘째자리까지만 출력

3. 일자 컬럼을 기준으로 오름차순 정렬

 

Solution

WITH weekly_active_users AS (
SELECT d.order_date AS dt
     , COUNT(DISTINCT d.customer_id) AS dau
     , (
        SELECT COUNT(DISTINCT w.customer_id)
        FROM records w 
        WHERE w.order_date BETWEEN DATE_SUB(d.order_date, INTERVAL 6 DAY) AND d.order_date
       ) AS wau
FROM records d 
WHERE d.order_date BETWEEN '2020-11-01' AND '2020-11-30'
GROUP BY dt
ORDER BY dt

)
SELECT dt
     , dau
     , wau
     , ROUND(dau/wau, 2) AS stickiness 
FROM weekly_active_users
ORDER BY dt

 

✅ weekly_active_users CTE:

• r1.order_date를 기준으로 일간 활성 사용자 수 dau 집계한다.
• 서브쿼리를 사용하여 r1.order_date 기준으로 지난 7일 동안의 고객 수를 계산한다. 

✅ 최종 SELECT절:

• CTE에서 계산된 dau, wau를 기반으로 고착도 지표를 계산하고 일자별로 오름차순 정렬한다.

 

피드백

 

wau 계산하는 쿼리를 윈도우 함수와 ROWS BETWEEN 구문으로 작성.

💡이는 단순히 행 단위로 데이터 범위를 지정하기 때문에 잘못된 로직이다.

 

 

✔️0218 복습