데이터리안 - SQL 데이터 분석캠프 실전반- 2주차: solvesql 연습문제

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

문제

온라인 쇼핑몰의 Stickiness

풀이

나의 풀이

SELECT order_date AS dt
     , COUNT(DISTINCT customer_id) AS dau
     , COUNT(DISTINCT DATE_??)
FROM records
WHERE order_date BETWEEN '2020-11-01' AND '2020-11-30'
GROUP BY dt

: wau 구현에서 막혀부렀다!

선생님 풀이

SELECT d.order_date AS dt
     , COUNT(DISTINCT d.customer_id) AS dau
     , COUNT(DISTINCT w.customer_id) AS wau
     , ROUND(COUNT(DISTINCT d.customer_id) / COUNT(DISTINCT w.customer_id), 2) AS stickiness
FROM records AS d 
  LEFT JOIN records AS w ON w.order_date BETWEEN DATE_ADD(d.order_date, INTERVAL -6 DAY) AND d.order_date
WHERE d.order_date BETWEEN '2020-11-01' AND '2020-11-30'
GROUP BY d.order_date
ORDER BY dt

내용

• wau는 d.order_date의 6일전부터 d.order_date까지의 d.customer_id 카운트를 전부 합한 것.
  ->

LEFT JOIN records AS w ON w.order_date BETWEEN DATE_ADD(d.order_date, INTERVAL -6 DAY) AND d.order_date