[TIL] 데이터분석 데브코스 25일차 - 트랜잭션/고급SQL문법/과제리뷰

지난 과제 리뷰

과제 #2 해설

- 목표 : 사용자별로 세션 생성 시간 기준 처음 채널과 마지막 채널 알아내기

 

- 방법 1 : CTE를 빌딩블록으로 사용

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 = lastluserid AND last.seq = 1 -- last.seq = 1을 WHERE절로 뺄수있지만
WHERE first.seq = 1; -- 일반적으로 FROM절 테이블과 관련된 조건을 WHERE로 뺀다.

 

- 방법 2: `JOIN` 사용

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 = lastluserid AND last.seq = 1 -- last.seq = 1을 WHERE절로 뺄수있지만
WHERE first.seq = 1; -- 일반적으로 FROM절 테이블과 관련된 조건을 WHERE로 뺀다.

 

- 방법 3: `GROUP BY` 사용

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 rn1,
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
   )
   GROUP BY 1;

 

 

- 방법 4: `FIRST_VALUE`, `LAST_VALUE` 사용하기

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.sessiㅐㅜ_timestamp B ON A.sessionid = B.sessionid;

 

과제 #3 해설

- 목표 : Gross Revenue가 가장 큰 UserID 10개 찾기

 

- 방법 1. `GROUP BY` 사용하기

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;

※ `LIMIT 10` 의 문제점

마지막 10번째가 동점자일 경우, `LIMIT 10`을 사용하면 동점자 중 1명만 선택되고 나머지 동점자들은 선택되지 않음

 

 

- 방법 2. `SUM OVER` 사용하기

SELECT DISTINCT
	userID,
    SUM(amount) OVER(PARTITION BY usc.userid) -- 같은 userid를 갖는 레코드들 끼리 묶어서 그것들끼리 합함
FROM raw_data.session_transaction st
JOIN raw_data.user_session_channel usc ON st.sessionid = usc.sessionid
ORDER BY 2 DESC
LIMIT 10;

→ 동일한 수의 userID에 대해서 amount가 계산됨(`JOIN`된 결과에 따라 그만큼 userID가 반복됨)

→ 중복된 레코드가 나올 수 있기 때문에 `DISTINCT`를 사용한다. 

 

 

과제 #4 해설

- 목표 : raw_data.nps 테입르을 바탕으로 월별 NPS 계산

** NPS =  9~10점을 추천하겠다는 고객(promoter)의 비율 - 0~6점을 추천하겠다는 고객(detractor)의 비율

 

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 passive,
        COUNT(1) AS total_count
    FROM raw_data.nps
    GROUP BY 1
    ORDER BY 1
);

 

트랜잭션 (Transaction)