[solvesql] 온라인 쇼핑몰의 Stickiness

구해야하는 컬럼은 dt , dau , wau , dau/wau 지표. 

wau 를 구하는 방식에서 JOIN 이 머릿속에서 구조화가 되지 않아서 너무 힘들었기에 다시 한번 정리한다. 

 

1. 먼저 11월 한달간의 dau를 구하는것은 너무나도 쉽다. 

2. 그러나 , wau 를 구하려면 dt 기준에서 7일간의 dt 를 JOIN 을 해야하는데 

 

먼저 정답인 쿼리는 

select 
  dt 
  ,dau
  ,wau 
  ,round(dau/wau,2) as stickiness
from (
  select d.order_date as dt , 
          count(distinct d.customer_id) as dau , 
          count(distinct w.customer_id) as wau  
  from records d join records w on w.order_date between date_add(d.order_date,  interval -6 day) and d.order_date
  where date_format(d.order_date, '%Y-%m-%d') between '2020-11-01' and '2020-11-31'
  group by dt 
) as t 
order by dt asc ;

 

 

<구조화 연습> 

SQL 분석 문제를 풀 때 먼저는 머릿속에 습관 처럼 넣어두어야 할 항목을 정리해봤다.

1. 집계 기준은 무엇인가? 

-> 하루인지, 월인지, 가입일인지 등 

2. 집계 대상 구간을 어떻게 잡을 것인가? 

-> 이전 7일? 이후 30일? 해당 월? 등 

3. 기준 테이블 vs 대상 테이블을 나누기 즉, 항상 분리된 테이블처럼 사고해야한다. 

 

위의 문제로 다시 한번 정리하자면 

기준 테이블 (d)	대상 테이블(w)	JOIN 조건	목적
d.order_date	w.order_date (7일치)	w.order_date 뭔데? d.order_date - 6 AND d.order_date 사이에 포함된 date 모두야!	wau의 구간을 잡기 위해 JOIN

 

 

SQL은 구조화만 잘해서, 어떻게 문제를 풀지 잘 정의해두면 못 풀 문제는 없다.. ㅠ 하지만 구조화하는게 너무 어려울뿐..