[SQL] 숙제2

Peter·2021년 7월 13일

SQL

목록 보기

14/19

사용자별로 처음 채널과 마지막 채널 알아내기

ROW_NUMBER vs FIRST_VALUE/LAST_VALUE

SELECT ts, channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
WHERE userid = 251
ORDER BY 1

유저 세션 채널과 타임스탬프를 세션아이디를 매개로 엮음
userid가 251인 것만 가져옴
타임스탬프를 기준으로 시간정렬을 하면 채널 컬럼의 처음과 마지막이 사용자별 처음과 마지막 채널이 됨

CTE를 빌딩블록으로 사용

%%sql

WITH first AS (
   SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
   FROM raw_data.user_session_channel usc
   JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
), last AS (
   SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
   FROM raw_data.user_session_channel usc
   JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)   
SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM first
JOIN last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

WITH을 통해서 테이블을 조인하는 조합으로 하는 first, last 테이블을 만들어주고 차이는 타임스탬프 기준으로 오른차순, 내림차순
seq 컬럼을 새로 만들고 순서대로 1부터 숫자가 기입됨
WHERE로 seq = 1을 가져오면 처음과 끝이 한테이블로 따라오게 됨

JOIN 방식

%%sql

SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM (
  SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) first
JOIN (
  SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)  last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

JOIN을 사용해서 유저아이디, 타임스탬프, 채널, seq 이름으로 ts 기준으로 오름차 정렬
테이블 하나는 내림차 정렬로 해줌
WHERE first.seq = 1 조건으로 유저아이디와 퍼스트채널, 라스트채널을 가져와줌
가장 위 레코드에 처음 채널과 마지막 채널이 기록돼있음

GROUP BY

%%sql

SELECT userid,
 MAX(CASE WHEN rn1 = 1 THEN channel END) first_touch,
 MAX(CASE WHEN rn2 = 1 THEN channel END) last_touch
FROM (
  SELECT userid,
    channel,
    (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY  st.ts asc)) AS rn1,
    (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY  st.ts desc)) AS rn2
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)
GROUP BY 1;

유저세션채널과 타임스탬프를 세션아이디를 기준으로 묶고
유저아이디, 채널, rn1(타임스탬프 기준으로 유저아이디를 오더한 파티션), rn2(타임스탬프 기준으로 유저아이디를 내림 정렬한 파티션)을 만들어주고
유저아이디, rn1 = 1, rn2 = 1이면 채널을 퍼스트터치, 라스트터치이름으로 테이블 만들어줌

FIRST_VALUE/LAST_VALUE

%%sql

SELECT DISTINCT
    A.userid,
    FIRST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS First_Channel,
    LAST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS Last_Channel
FROM raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B ON A.sessionid = B.sessionid
ORDER BY 1;

rows between ~ and ~를 사용해서 최종출력될값의 맨 처음(unbounded preceding) 그리고 맨 마지막(unbounded following)을 구하고
FIRST_VALUE, LAST_VALUE 함수를 사용해서 첫번째, 마지막 밸류를 넣어줌

Gross Revenue가 가장 큰 UserID 10개 찾기

GROUP BY

%%sql

SELECT
    userID,
    SUM(amount)
FROM raw_data.session_transaction st
LEFT JOIN raw_data.user_session_channel usc ON st.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

트랜잭션은 전부 정렬하고 LEFT JOIN으로 유저세션채널을 트랜잭션과 이어지는대로 붙임
GROUP BY로 userID 기분으로 amount의 합을 컬럼으로 구하고
어마운트를 내림차순 으로 정렬시킴

SUM OVER

%%sql

SELECT DISTINCT
    usc.userid,
    SUM(amount) OVER(PARTITION BY usc.userid)
FROM raw_data.user_session_channel AS usc
JOIN raw_data.session_transaction AS revenue ON revenue.sessionid = usc.sessionid  
ORDER BY 2 DESC 
LIMIT 10;

파티션 바이를 통해 userid를 기준으로 어마운트를 합한 값을 넣어줌

raw_data.nps 테이블을 바탕으로 월별 NPS계산

테이블 정보

고객들이 0(의향없음) 에서 10(의향 아주 높음)
- detractor (비추천자): 0에서 6
- passive (소극자): 7이나 8
- promoter (홍보자): 9나 10
- NPS = promoter퍼센트 - detractor퍼센트

CASE WHEN

%%sql

SELECT month, 
  ROUND((promoters-detractors)::float/total_count*100, 2) AS overall_nps
FROM (
  SELECT LEFT(created, 7) AS month,
    COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
    COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
    COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END) As passives,
    COUNT(1) AS total_count
  FROM raw_data.nps
  GROUP BY 1
  ORDER BY 1
);

프로모터스 카운트, 디트랙터 카운드, 패시브 카운트, 전체 카운트 를 구하고 created(생성날짜) 에 붙여줌
프로모터에 디트랙터스를 빼준값을 토탈카운트로 나눠주고 100을 곱하고 소수점 3번째에서 반올림 해준 값을 overall_nps 컬럼으로 넣어줌

WHEN

%%sql

SELECT LEFT(created, 7) AS month,
  ROUND(SUM(CASE
    WHEN score >= 9 THEN 1 
    WHEN score <= 6 THEN -1 END)::float*100/COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;