[0307 TIL] (최종 프로젝트 2일차) / sql 심화 2강(any-order funnel, open funnel, closed funnel)

오늘 한 것

 

sql 1강 녹강으로 다시 복습,

최종 프로젝트 데이터 탐색(지방vs도시 이커머스 소비패턴분석)-> 자료 없어서 패스

인도 블링킷 배달회사 데이터 사용하기로 결정 후 products 파일에 집중해서 eda 진행

심화 2강 라이브수강(클로즈퍼널로직 공부했는데 너무너무 재밌게 들었다..!!!)

---

 

라이브코딩 해설

 

https://datalemur.com/questions/rolling-average-tweets

 

Twitter SQL Interview Question: 3-Day Rolling Tweets

이 문제 너무 어려워서 10분 고민했는데 fail.. 시간을 더 들여서 고민하고 싶은데 시간없었던게 아쉬웠다 

다시 꼼꼼히 볼 예정

 

혹시 함정 넣는다면->중 에 day 6,7 이런거에 널 값으로 구멍 나 있을 때 0넣어서 행 만들어 해결하는 코드

WITH date_series AS (
    SELECT generate_series(
        (SELECT min(tweet_date) FROM tweets),
        (SELECT max(tweet_date) FROM tweets),
        '1 day'
    ) AS tweet_date
),
all_dates_users AS (
    SELECT d.tweet_date, u.user_id
    FROM date_series d
    CROSS JOIN (SELECT DISTINCT user_id FROM tweets) u
),
history AS (
    SELECT 
        adu.user_id,
        adu.tweet_date,
        COALESCE(t.tweet_count, 0) AS tweet_count
    FROM all_dates_users adu
    LEFT JOIN tweets t 
    ON adu.user_id = t.user_id 
    AND adu.tweet_date = t.tweet_date
)
-- 방법1 
SELECT 
    user_id, 
    tweet_date,
    round(AVG(tweet_count) OVER (
        PARTITION BY user_id 
        ORDER BY tweet_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        -- ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING -- 오늘을 포함하지 않는 과거 3일만의 평균을 구해야한다면
    ),2) AS rolling_avg_3d
FROM history
ORDER BY user_id, tweet_date;



-- --- 방법 2
-- SELECT 
--     h1.user_id, 
--     h1.tweet_date,
--     COALESCE(AVG(h2.tweet_count), 0) AS rolling_avg_3d
-- FROM history h1
-- LEFT JOIN history h2 
--     ON h1.user_id = h2.user_id 
--     AND h2.tweet_date BETWEEN h1.tweet_date - INTERVAL '2 days' AND h1.tweet_date  
-- GROUP BY h1.user_id, h1.tweet_date
-- ORDER BY h1.user_id, h1.tweet_date;

 

---

Sql 기습퀴즈

 

내가 작성한 답

(1) Step A: 2명, step B : 2명, step C : 4명

(2) step A :2명, step B : 2명, step C : 1명

 

정답

2,2,3

2,1,1

 

any-order funnel (내가 착각한 것)

->이게 224인거고, 어디서 시작해도 상관없는거니까 4인거고

 

open funnel은 시작이 중요

-> 무조건 x 0 x일때  세지 않음. c에 도착할때도 b가 비어있으니까 

 

폐쇄형 퍼널 Closed Funnel
: 시작이 First step이면서 step을 순서대로 접속한 횟수

 

무조건 시작이 중요하다, a아닌애들은 제껴도 됨

A : 2

B:1개

C:1개

 

---

Closed funnel 

 

폐쇄형 퍼널 분석 쿼리 작업전 구조화 하기 

-> 직접 이해하고 손으로 연습해본 후 쿼리 작성하니 이해가 너무 잘 되고 수업이 재미있었다..!

 

윈도우에서 x 빼고 1->4 갔으면 다 살리기

4에서 없어지니까 null 4->null

다 대괄호로 묶고, 그 차이가 1초과인것은 지우기

(1 접속했던 애들은 잊지말고 예외로 챙겨서 넣기)

헤드와 테일만 갖고 합집합시킨 후,

->오른쪽 어레이표에서 1로 시작하지 않는건 버리기

그러면 모든 걸 다 간 고객을 구할 수 있음

 

 

# 폐쇄형 퍼널 분석: 
WITH window as (
  SELECT user_id, window_num, event_time as window_st_ts, lead(event_time,1,current_timestamp) over (partition by user_id order by window_num) as window_end_ts
  FROM(

      SELECT *, rank () over (partition by user_id order by event_time) as window_num
      FROM event_logs
      WHERE funnel_step = 1
      ORDER BY user_id, event_time
  ) 
)
, funnel_log AS (
    SELECT l.user_id, l.event_name, funnel_step, window_num
    , lead(funnel_step,1,null) over (partition by l.user_id, window_num order by funnel_step) as next_funnel_step
    FROM event_logs l 
      JOIN window w 
      on l.user_id = w.user_id 
      AND l.event_time >= window_st_ts AND l.event_time < window_end_ts 
)
, funnel_step AS (
  SELECT user_id, window_num, replace(GROUP_CONCAT(DISTINCT steps),',','') AS steps
  FROM (

  SELECT 
  user_id, window_num
  , GROUP_CONCAT(funnel_step) AS steps
  FROM funnel_log
  WHERE next_funnel_step IS NULL OR (next_funnel_step - funnel_step) = 1
  GROUP BY user_id, window_num

  UNION 

  SELECT 
  user_id, window_num
  , GROUP_CONCAT(next_funnel_step) AS steps
  FROM funnel_log
  WHERE next_funnel_step IS NULL OR (next_funnel_step - funnel_step) = 1
  GROUP BY user_id, window_num
  )
  WHERE steps like '1%'
  GROUP BY  user_id, window_num

)

SELECT count(distinct step_1) as step_1
, count(distinct step_12) as step_12
, count(distinct step_123) as step_123
, count(distinct step_1234) as step_1234
, count(distinct step_12345) as step_12345
, count(distinct step_123456) as step_123456
, count(distinct step_1234567) as step_1234567
, count(distinct step_12345678) as step_12345678
, count(distinct step_123456789) as step_123456789
, count(distinct step_12345678910) as step_12345678910

FROM ( 
SELECT DISTINCT user_id, window_num, steps, 
		case when steps like '1%' then user_id else null end as step_1,
		case when steps like '12%' then user_id else null end as step_12,
		case when steps like '123%' then user_id else null end as step_123,
		case when steps like '1234%' then user_id else null end as step_1234,
		case when steps like '12345%' then user_id else null end as step_12345,
		case when steps like '123456%' then user_id else null end as step_123456,
		case when steps like '1234567%' then user_id else null end as step_1234567,
		case when steps like '12345678%' then user_id else null end as step_12345678,
		case when steps like '123456789%' then user_id else null end as step_123456789,
		case when steps like '12345678910%' then user_id else null end as step_12345678910		

FROM funnel_step
)

 

주말에 꼼꼼하게 더 복습할 것, 실습도 해볼 것!! + 라이브코딩도 밀리지 말고 챙기기