[solvesql] EASY & 보통 문제 정복하기 (온라인 쇼핑몰의 Stickiness )

| 배송 예정일 예측 성공과 실패 (MY SQL) 

 

▣ 조건 1 : 2017년 1월 한달동안 발생한 주문 중 

▣ 조건 2 : 배송 예정 시각안에 도착했던 건인가 아닌가 

▣ 조건 3 : 구매 날짜별로 건수 

▣ 조건 4 : 구매 날짜 오름차순 

▣ 조건 5 : 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외

 

실패 코드 및 이유 분석 

-- 2017년 1월 , 구매 일자별, 배송이 제대로 된건과 안된건 
-- 구매 날짜 기준으로 오름차순 

select date_format(order_purchase_timestamp,'%Y-%m-%d' ) as 'purchase_date', 
CASE WHEN and order_delivered_customer_date <= order_estimated_delivery_date THEN count(*) as success
    ELSE 
from olist_orders_dataset
where order_status = 'delivered' 
group by date_format(order_purchase_timestamp,'%Y-%m-%d' )
having purchase_date like '2017-01%'
order by 1

 

▶ CASE WHEN  THEN ELSE  한 줄로 끝내려했다 ㅋㅋㅋ 그러다가 급 변경 ㅎ

 

보완 및 성공

select date_format(order_purchase_timestamp, '%Y-%m-%d') as purchase_date, 
count(case when order_delivered_customer_date <= order_estimated_delivery_date then order_id end) as success, 
count( case when order_delivered_customer_date > order_estimated_delivery_date then order_id end) as fail
from olist_orders_dataset
group by date_format(order_purchase_timestamp, '%Y-%m-%d')
having purchase_date like '2017-01%'
order by 1

▶ 피벗테이블 개념으로 CASE WHEN 구문 접근 :  실제 배송완료 시간 <= 예정시간  일 경우 성공 /  반대일 경우 실패 

 

공부

◈ MYSQL 에선 date_format(  date, 형식 ) 을 사용 

    (참고 : 블로그)

 

---

| 서울 북부 지방법원 따릉이 정류소 

 

▣ 조건 1 : '서울북부지방법원' 정류소보다 북쪽에 있는 정류소 정보를 출력

 

select *
from station
where lat > (select lat from station where name = '서울북부지방법원')

 

---

| 온라인 쇼핑몰의 Stickiness  🤷‍♀️💪🏻🤦🏻‍♀️

(이 문제 하나가지고 몇시간을 허비했는지.. 허탈 그 잡채였던 문제 ㅎ )

 

 

▣ 조건 1 : '활성 고객' = 해당 기간 동안 한번이라도 주문한 고객 

▣ 조건 2 : 2020년 11월 한달 동안의 일별 DAU, WAU, Stickiness( DAU/WAU) 구하기 

▣ 조건 3 : 고객들이 한번도 주문하지 않은 날은 집계에서 제외 

▣ 조건 4 : dt 오름차순으로 정렬 

 

 

1. 집계 시작일 지정 : 기준 날짜 - 6일 ( -7일이라고 쿼리 짰다가 오답의 향연 잡채였음)

2. 집계 종료일 지정 : 기준 날짜 

3. 날짜별 dau, wau 구하기 

 

를 생각하며 문제를 풀어봤다.

집계 시작일 지정은 Date_sub( 기준 날짜, INTERVAL 숫자 DAY )  함수를 썼으나 group by 집계에서 이제 지옥을 맛 본 문제.

 

실패 코드 및 이유 분석 

 

실패 1

select order_date, count(distinct order_id) as dau, 
count(distinct case when order_date between date_sub(order_date,INTERVAL 6 DAY ) and order_date then order_id end) as wau
from records
group by order_date
having order_date like '2020-11%' and dau > 0

 

▶ 다시 보니 말도 안되는 코드 그 잡채 

▶ 심지어 count 를 customer_id로도 안씀 ^^;; 

 

위 코드시 위와 같이 나온다. 

 

 

실패 2

select dt, dau, wau, round(dau/wau, 2) as stickiness
from (
select order_date as dt , count(distinct customer_id) as dau, 
sum(count(distinct customer_id)) over(order by order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as wau
from records
group by order_date) t 
having dt like '2020-11%' and dau > 0

 

▶ 오답인 쿼리. 뜯어서 왜 답이 아닐까..라고 생각했는데 아무래도 중복 customer_id가 들어가서가 아닐까싶다

▶ like 위대로 할 경우 2020-11-07의 WAU값은 11월1~7일의 DAU의 누적합인데, 만약 A가 11월 2일, 3일에 주문을 했다면 저 값에 두번 다 count 된다. WAU는 저 기간 내 고객 수다보니 중복되면 X (왜 오답인지 이유를 찾는데 시간이 많이 할애되는 나는...SQL 초보) 

 

실패 2 답

 

보완 및 성공

with DAU as (
select order_date, count(distinct customer_id) as dau
from records 
group by order_date)


select dt , dau, wau, round(dau/wau, 2) as stickiness
from (
select d.order_date as dt, 
d.dau as dau, 
count(distinct customer_id) as wau
from DAU d join records r
on r.order_date between date_sub(d.order_date, INTERVAL 6 DAY) and d.order_date 
group by d.order_date
having dt like '2020-11%' ) t

 

▶ 정말 열받는게 처음에 INTERVAL 7로 쿼리 돌렸다가 오답나와서 또 생각만 몇십분하고 ^_^ ... 그러다 기간을 잘 못 세팅 한걸 깨닫는 순간 =  허탈함 100% 

▶ 윈도우 함수가 아닌 JOIN을 통해 답을 냈다

▶ 난 메인절에서 기간을 잡아줬는데, 아래 쿼리처럼 WITH AS 절에 기준 날짜를 잡아주면 메인절의 쿼리가 좀 더 간단해진다

 

 

WITH table1 AS (
 SELECT order_date AS dt # 기준 날짜 
    , DATE_SUB(order_date, INTERVAL 6 day) st  # 7일 전 날짜 (start의 st) 
    , COUNT(DISTINCT customer_id) AS DAU
 FROM records
 GROUP BY dt, week
)

SELECT t.dt as dt , t.dau as dau, count(DISTINCT customer_id) as wau, 
round(t.dau/count(DISTINCT customer_id),2) as stickiness
from table1 t join records r 
on r.order_date between t.st and t.dt
group by t.dt, t.dau 
having dt like '2020-11%'

 

공부

◈  Window 함수 다시 공부  및 JOIN 

   참고 : 블로그

 

---

 

SQL 한참 할 때 WITH AS 좋아했기에, 간만에 보니 더 반갑다. 다시 WITH AS 테이블 쓰는 습관을 들여야지 

아직도 JOIN은.... 너무 어려운것 ㅠ 

 

 

+ 문제출처 :  https://solvesql.com/  (위 문제들은 강의 수강생들에게만 오픈된 문제입니다)