DAU, WAU, MAU 구하기

-- 공통: 일 버킷
WITH e AS (
  SELECT user_id, DATE(event_date) AS d
  FROM events
)

-- ① DAU: 날짜별 고유 유저 수
SELECT d, COUNT(DISTINCT user_id) AS dau
FROM e
GROUP BY d;

-- ② MAU: 월 버킷(월 첫째날) 기준 고유 유저 수
SELECT DATE_FORMAT(d, '%Y-%m-01') AS m,
       COUNT(DISTINCT user_id)     AS mau
FROM e
GROUP BY 1;

-- ③ WAU: 주 버킷(월요일 시작 예시) 기준 고유 유저 수
SELECT STR_TO_DATE(CONCAT(YEARWEEK(d, 3), ' Monday'), '%X%V %W') AS w_start,
       COUNT(DISTINCT user_id) AS wau
FROM e
GROUP BY 1;

최근 30일 (DAU 예시)

WITH e AS (
  SELECT user_id,
         DATE(event_date) AS d
  FROM events
  WHERE event_date >= CURDATE() - INTERVAL 30 DAY
)
SELECT d, COUNT(DISTINCT user_id) AS dau
FROM e
GROUP BY d
ORDER BY d;

최근 3개월 (MAU 예시)

WITH e AS (
  SELECT user_id,
         DATE(event_date) AS d
  FROM events
  WHERE event_date >= CURDATE() - INTERVAL 3 MONTH
)
SELECT DATE_FORMAT(d, '%Y-%m-01') AS m,
       COUNT(DISTINCT user_id)     AS mau
FROM e
GROUP BY 1
ORDER BY m;

최근 12주 (WAU 예시, 월요일 시작)

WITH e AS (
  SELECT user_id,
         DATE(event_date) AS d
  FROM events
  WHERE event_date >= CURDATE() - INTERVAL 12 WEEK
)
SELECT YEARWEEK(d, 3) AS yw,  -- (3) 옵션: 월요일 시작
       COUNT(DISTINCT user_id) AS wau
FROM e
GROUP BY yw
ORDER BY yw;

통합

WITH params AS (
  SELECT DATE('2023-01-01') AS start_date, DATE('2023-04-01') AS end_date
),
e AS (
  SELECT ev.user_id, DATE(ev.event_date) AS d
  FROM events ev
  JOIN params p
    ON ev.event_date >= p.start_date
   AND ev.event_date <  p.end_date        -- 반개방 구간
),
dau AS (
  SELECT d, COUNT(DISTINCT user_id) AS dau
  FROM e
  GROUP BY d
),
wau AS (
  SELECT YEARWEEK(d, 3) AS yw,
         STR_TO_DATE(CONCAT(YEARWEEK(d, 3), ' Monday'), '%X%V %W') AS w_start,
         COUNT(DISTINCT user_id) AS wau
  FROM e
  GROUP BY 1,2
),
mau AS (
  SELECT DATE_FORMAT(d, '%Y-%m-01') AS m,
         COUNT(DISTINCT user_id) AS mau
  FROM e
  GROUP BY 1
)
SELECT d.d,
       d.dau,
       w.wau,
       m.mau,
       ROUND(d.dau / NULLIF(m.mau, 0), 4) AS stickiness_d_over_m,
       ROUND(w.wau / NULLIF(m.mau, 0), 4) AS stickiness_w_over_m
FROM dau d
JOIN wau w  ON w.yw = YEARWEEK(d.d, 3)
JOIN mau m  ON m.m  = DATE_FORMAT(d.d, '%Y-%m-01')
ORDER BY d.d;